Pivot transformation using t-sql [closed]

问题:

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

回答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

回答2:

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 table1
pivot (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 grouping elements 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 values i.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 names i.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 in i.e.: [Pepsi], [Cake],[Apple]

Your query becomes:

SELECT location,product,qty
FROM #temp
UNPIVOT(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.