Sunday, September 18, 2011

UnPivoting data in SQL Server

There are some situations when we need to do the data comparison row level. One of the solution of this kind of situation is to do the "Unpivoting" of data which will apparently move the records under a specific column and then we can do the filter of the data by using the "Where" clause.

Here is an example.
Lets take the below sample code

declare @Test as table(id int, col1 int, col2 int, col3 int, col4 int)
insert into @Test values(1, 100, 500, 300, 200)
insert into @Test values(2, 600, 300, 500, 400)
insert into @Test values(3, 400, 100, 800, 300)

select * from @Test

The expected output from the above table is to get the each rows max value. This means the output should be
ID MaxVal
1 500
2 600
3 800

Since in this case we need to do the comparison at row, the first thing which should be clicked is unpivoting of data and then do the normal filtering. To get this output we will use the SQL Server Unpivot command as below.

SELECT id,Max(colVal) as MaxVal
FROM
(SELECT * FROM @Test) T
UNPIVOT
(
ColVal FOR columnnames in(col1,col2,col3,col4)
) as UnPvtTable
Group by ID

Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach

Here is the link available for you to download the white paper on Implementing the parallel Data warehouse using Kimball approach.
Download White Paper here