SQL - 转置 [英] SQL - Transpose

问题描述

我有一些小问题，我一直试图在 SQL 中解决.我有一个包含项目编号、属性名称和属性值的表.每个项目编号可能具有与其属性值关联的相同或不同的属性名称集.我想要做的是每行拥有唯一的项目编号，并将属性名称转置为我的列，在这种情况下将属性值放置在相应的属性名称标题下.

下图是当前的tbl_ICC表:

我想要得到的是这个视图:

我在这里和其他来源阅读了几篇类似的帖子，我想到的最接近的是这个 SQL 查询:

 选择*从(选择 [ITEM_NUMBER]，[ATTR_DISPLAY_NAME]，[ATTRIBUTE_VALUE]FROM tbl_ICC) 作为源表枢(([ATTRIBUTE_VALUE])FOR [ATTR_DISPLAY_NAME] IN(从 tbl_ICC 中选择* [ATTR_DISPLAY_NAME]))作为数据透视表；

由于某种原因，我不断收到语法错误，并且查询没有给我任何东西.为了将视图从 image1 转换为 image 2 上的视图，我需要在此处进行哪些更改?

提前谢谢你.

解决方案

您的原始查询中有一些语法错误

选择 *从(选择 [ITEM_NUMBER]，[ATTR_DISPLAY_NAME]，[ATTRIBUTE_VALUE]FROM tbl_ICC ) 作为源表PIVOT(最大值([ATTRIBUTE_VALUE])FOR [ATTR_DISPLAY_NAME] IN ([color],[size] )) -- <<在此处添加更多 Attr 显示名称作为数据透视表；

<块引用>

编辑 - 动态版本

声明@SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([ATTR_DISPLAY_NAME]) From tbl_ICC Order by 1 For XML Path('')),1,1,'')选择@SQL = '选择 *从 (选择 [ITEM_NUMBER]，[ATTR_DISPLAY_NAME]，[ATTRIBUTE_VALUE]来自 tbl_ICC) 一个Pivot (max(ATTRIBUTE_VALUE) For [ATTR_DISPLAY_NAME] in (' + @SQL + ') ) p'执行(@SQL)；

i have small issues i have been trying to figure out in SQL. I have a table with Item Numbers, Attribute Names and Attribute values. Each Item Number might have same or different set of Attribute Names associated with its Attribute values. What i am trying to do is to have unique Item Number per row and transpose Attribute Names to be my columns and Attribute Values to be placed under corresponding Attribute Name header in that case.

On the image below is the current tbl_ICC table:

What I am trying to get is this view:

I read several similar posts on here and other sources and the closest i came up is this SQL query:

  SELECT*
  FROM
  (SELECT [ITEM_NUMBER],
      [ATTR_DISPLAY_NAME],
      [ATTRIBUTE_VALUE]
  FROM  tbl_ICC) AS SourceTable
  PIVOT 
  (([ATTRIBUTE_VALUE])
  FOR   [ATTR_DISPLAY_NAME] IN ( Select* [ATTR_DISPLAY_NAME] FROM tbl_ICC))
  AS PivotTable; 

For some reason i keep getting errors with syntax and the query is not giving me anything. What do I need to change here in order to convert the view from image1 to view on image 2?

Thank you in advance.

解决方案

You have some syntax errors in your original query

SELECT * 
  FROM
  (SELECT [ITEM_NUMBER],
          [ATTR_DISPLAY_NAME],
          [ATTRIBUTE_VALUE]
    FROM  tbl_ICC ) AS SourceTable 
  PIVOT (max([ATTRIBUTE_VALUE])
   FOR   [ATTR_DISPLAY_NAME] IN ([color],[size] ))   -- << Add More Attr Display Name Here
  AS PivotTable; 

EDIT - Dynamic Version

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([ATTR_DISPLAY_NAME]) From tbl_ICC  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select * 
From (
       Select [ITEM_NUMBER],
              [ATTR_DISPLAY_NAME],
              [ATTRIBUTE_VALUE]
        From  tbl_ICC  
     ) A
 Pivot (max(ATTRIBUTE_VALUE) For [ATTR_DISPLAY_NAME] in (' + @SQL + ') ) p'
Exec(@SQL);

这篇关于SQL - 转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！