Pivot table data in tabular format, first column should be column name and rest should be value

Question

I just want to compare products that's why I need to show products with there attributes in column format. The First column will show attributes and rest of column shows attributes value for each column.

Simple select statement is as follows

SELECT  product_Id,product_Name,product_Price ,product_Description,product_weight  
from tblProduct
WHERE  product_Id in (1139,1140,1144)

Now I want to show my output in following way

I know that I need to use pivot to get that desired output. and i have tried in following way

SELECT MAX(ISNULL([1139],'')),MAX(ISNULL([1140],'')),MAX(ISNULL([1144],'')) 
FROM
(
  SELECT product_Id,product_Name,product_Price,product_Description,product_weight 
  FROM tblProduct where product_Id in (1139,1140,1144)
)s 
PIVOT
(
  MAX(product_Name)
  FOR product_Id in ([1139],[1140],[1144])
) AS PVT

but this only done for a single column but i needed for all attributes.

Answer 1

Since you are attempting to aggregate for attributes that exist in multiple columns, that is an indicator that you will need to apply both the UNPIVOT and the PIVOT functions.

The UNPIVOT will take your column values and convert them to rows. The code to unpivot will be similar to this:

select product_id, header, value
from
(
  select product_id, 
    product_name, 
    cast(product_price as varchar(10)) product_price, 
    product_weight
  from tblProduct
) p
unpivot
(
  value
  for header in (product_name, product_price, product_weight)
) unp

See SQL Fiddle with Demo. You will notice that there is a subquery in this that converts the product_price column to a varchar. This is because the datatypes of the columns you want in rows must be the same. So you might have to perform data conversions to get this to work properly.

The unpivot generates a result that looks like this:

| PRODUCT_ID |         HEADER | VALUE |
---------------------------------------
|        141 |   product_name |  A141 |
|        141 |  product_price |   200 |
|        141 | product_weight | 200gm |

Once the data is in the rows, you can apply the PIVOT function to the product_id column values.

select header, [141], [142], [143], [144]
from
(
  select product_id, header, value
  from
  (
    select product_id, 
      product_name, 
      cast(product_price as varchar(10)) product_price, 
      product_weight
    from tblProduct
  ) p
  unpivot
  (
    value
    for header in (product_name, product_price, product_weight)
  ) unp
) d
pivot
(
  max(value)
  for product_id in ([141], [142], [143], [144])
) piv

See SQL Fiddle with Demo. This gives a result:

|         HEADER |   141 |   142 |   143 |   144 |
--------------------------------------------------
|   product_name |  A141 |  A142 |  A143 |  A144 |
|  product_price |   200 |   300 |  4000 |  5000 |
| product_weight | 200gm | 300gm | 400gm | 100gm |

The above version will work great if you have a known number of product_id values that you want as columns. But if you have an unknown number, then you will want to implement dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(product_id) 
                    from tblProduct
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT header, ' + @cols + ' 
              from 
             (
                select product_id, header, value
                from
                (
                  select product_id, 
                    product_name, 
                    cast(product_price as varchar(10)) product_price, 
                    product_weight
                  from tblProduct
                ) p
                unpivot
                (
                  value
                  for header in (product_name, product_price, product_weight)
                ) unp
            ) x
            pivot 
            (
                max(value)
                for product_id in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. This will generate the same result as the static/hard-coded version of the query.