Pivot transformation using t-sql

Question

In SSIS there is a task called Pivot transformation that changes columns into rows, but how to do the same task in sql server using t-sql ?

this is my sample table

location product qty
-----------------------
delhi     PEPSI   100
GURGAON   CAKE    200
NOIDA     APPLE   150
delhi     cake    250

so after the pivot transformation ON location as setkey and product as pivot key using ssis tool the o/p becomes

location pepsi cake apple
delhi     100 null null
GURGAON   null 200 null 
NOIDA     null null 150 
delhi     null 250  null

Answer 1

Use the PIVOT table operator like this:

SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN([pepsi], [cake], [apple])
) as p;

• SQL Fiddle Demo

Note that:

• I used the MAX aggregate function with the qty, if you want to get the total sum use SUM or any other aggregate function instead.

• You have to write the values of the column to pivoted manually, if you want to do this dynamically instead of writing them manually, you have to use dynamic sql to do so.

Like this:

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

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(product)
                      FROM tablename
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN(' + @cols + ')) AS p;';

execute(@query);

• Updated SQL Fiddle Demo