SQL - Transpose

Question

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.

Answer 1

I have just done this for my own data and I found the following worked for me:

Change the following line :

    PIVOT 
  (([ATTRIBUTE_VALUE])
  FOR   [ATTR_DISPLAY_NAME] IN ( Select* [ATTR_DISPLAY_NAME] FROM tbl_ICC))

To:

PIVOT 
  (max(attribute_values) FOR   [ATTR_DISPLAY_NAME] IN ( Select* [ATTR_DISPLAY_NAME] FROM tbl_ICC))

Note I removed the Attribute_Value in the Pivot.

*note: you need to check your variable names to ensure what I have written is what you need.

Answer 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; 

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);

Answer 3

You should probably be using two tables, the first one using the item number as the primary key with color, speed, mass, etc as it's columns. The second table could then have a one to many relationship with the first table on the item number and have the attribute names and attribute values as its columns. This will not only allow you to print out the data you need (by using a join), but it will make database maintenance easier over time as things change.