SQL-转置 [英] SQL - Transpose

问题描述

我一直想在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.

下图是当前的tbl_ICC表:

On the image below is the current tbl_ICC table:

我想要得到的是这种观点:

What I am trying to get is this view:

我在此处和其他来源阅读了几篇类似的文章，而我提出的最接近的是此SQL查询:

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; 

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

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?

谢谢.

推荐答案

原始查询中存在一些语法错误

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; 

编辑-动态版本

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屋！