Archive

Posts Tagged ‘UNPIVOT’

TSQL – Transpose Data using Using PIVOT and UNPIVOT

20.04.2011 3 comments

Often we need to transpose the result set converting rows to columns. let’s look at an example. you can find the syntax on BOL. consider the following data:

image

We need to convert this to a pivot table, the out put should transpose the WeekIDs to rows from columns, the expected result is:

image

The PIVOT transforms all input rows to columns:

PIVOT
( 
    — Since all rows are unique, you can also use MIN, MAX…
    AVG(Sale) 
    FOR WeekID IN([1],[2],[3])
)    AS SaleByWeek — Alias for PIVOT Table

The above code will transform the columns ‘WeekID’ and ‘Sale’ to rows. we need to manually specify the no. of columns. so, if we have data for 5 Weeks, then we need to specify all rows that should be transposed as follows:

FOR WeekID IN([1], [2], [3], [4], [5])

The PIVOT functions only creates the result set, it does not output the results, we need a SELECT statement which will output from the PIVOT result set. so the final statement will be as follows to generate the required output.

SELECT  Region, 
        [1] As Week1, 
        [2] As Week2, 
        [3] As Week3
FROM    WeeklySales
PIVOT
( 
    — Since all rows are unique, you can also use MIN, MAX…
    AVG(Sale) 
    FOR WeekID IN([1], [2], [3])
)   AS SaleByWeek — Alias for PIVOT Table

Now, if you need to do a reverse operation, you need to use UNPIVOT function, which essentially performs the reverse operation from PIVOT. To transpose / UNPIVOT the above output we received after PIVOTing, we can use UNPIVOT as follows:

SELECT Region, SUBSTRING(WeekID,5,1) AS [WeekID], Sale
FROM 
    (SELECT Region, Week1, Week2, Week3
    FROM PivotWeeklySales) P
UNPIVOT 
    (Sale FOR WeekID IN 
    (Week1, Week2, Week3)
) AS UP  — Alias for UNPIVOT Table

The output of the above query will be the original data before PIVOTing.

image

Hope This Helps!

Vishal