Pivot transformation using t-sql [closed]

Use the PIVOT table operator like this:

SELECT *FROM tablenamePIVOT(  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 tablenamePIVOT(  MAX(qty)  FOR product IN(' + @cols + ')) AS p;';execute(@query);

• Updated SQL Fiddle Demo

A pivot request involves three logical processing phases, each with associated elements:

1. Grouping phase
2. Spreading phase
3. and an aggregation phase with an associated aggregation element and aggregate function.

So mapping these phases with requirement in your case:

1. Grouping has to be done on ‘Location’
2. spreading has to be done based on ‘Product’ column values with final column names as: ‘Pepsi’,’Cake’,’Apple’.
3. ‘Qty’ values are to be aggregated to produce intersecting values for grouping and spreading elements

Putting these values in standard Pivot statement:

SELECT ...FROM <source_table_or_table_expression>PIVOT(<agg_func>(<aggregation_element>)FOR <spreading_element>IN (<list_of_target_columns>)) AS <result_table_alias>

Your query becomes:

select location ,[PEPSI], [CAKE],[APPLE]from table1pivot (sum(qty)       for product       in ( [PEPSI], [CAKE],[APPLE])) AS T

It is important to note that with the PIVOT operator you do not explicitly specify the grouping elements,removing the need for GROUP BY in the query. The PIVOT operator figures out the groupingelements implicitly as all attributes from the source table (or table expression) that were not specified as either the spreading element or the aggregation element. So you must ensure that the source table for the PIVOT operator has no attributes besides the grouping, spreading, and aggregation elements,so that after specifying the spreading and aggregation elements, the only attributes left are those you intend as grouping elements. You achieve this by not applying the PIVOT operator to the original table directly but instead to a table expression that includes only the attributes representing the pivoting elements and no others.

select location ,[PEPSI], [CAKE],[APPLE]from (select location,product,qty       from table1 ) as SourceTable      pivot (sum(qty)           for product           in ( [PEPSI], [CAKE],[APPLE])) AS T

Hope this helps understanding Pivot operator better!!

EDIT: Added Unpivot operator concept:

Like Pivoting , Unpivoting also involves 3 logical phases:

1. Producing copies
2. Extracting elements
3. Eliminating irrelevant intersecting records

Putting these values in standard Unpivot statement:

SELECT ...FROM <source_table_or_table_expression>UNPIVOT(<target_col_to_hold_source_col_values>FOR <target_col_to_hold_source_col_names> IN(<list_of_source_columns>)) AS<result_table_alias>;

Mapping these phases with requirement in your case:

1. <target_col_to_hold_source_col_values> = name of the column that will hold source column valuesi.e.: to hold column values [Pepsi], [Cake],[Apple] i.e. 100,250 ... you want to have one single column as : Qty
2. <target_col_to_hold_source_col_names> = name of the column that will hold source column namesi.e.: to hold column names [Pepsi], [Cake],[Apple] you want to have one single column as : product
3. <list_of_source_columns> = names of columns in source table you are interested ini.e.: [Pepsi], [Cake],[Apple]

Your query becomes:

SELECT location,product,qtyFROM #tempUNPIVOT(qty        FOR product          IN([Pepsi],[Cake],[Apple])) AS U;

Where I have added results from above Pivot statement in a temporary table #temp.

Important point to note here is :Unpivoting a pivoted table cannot bring back the original table as pivoting results in loss of detailed information due to aggregation.