Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Transpose Data using Using PIVOT and UNPIVOT

TSQL – Transpose Data using Using PIVOT and UNPIVOT


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

  1. Bipin Malhotra
    24.11.2011 04:27 | #1

    This is one of the best answer so far, I have read online. No crap, just useful information. Very well presented. I had found another good collection of MS-SQL Server interview question and answer over internet. I want to share that post link…

    http://www.mindstick.com/Interview/883/What%20is%20PIVOT%20and%20UNPIVOT

  2. Ravi
    18.07.2012 09:28 | #2

    Great Tip, the best one for this situation. The others I found are simply too complicated and tend to beat around the bush. xcellent tip.

  3. Mangesh
    08.07.2015 12:31 | #3

    Awesome stuff!

  1. No trackbacks yet.