Tuesday, October 4, 2011

UNPIVOT in SQL SERVER with Example

Here in this example it shows the Max column value for the specific ID
how it is get by UnPIVOT let see :

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 ID,MAX(data) AS MaxValue
FROM
(
SELECT ID,Data
FROM
(SELECT ID, col1, col2, col3, col4
FROM @Test
) p
UNPIVOT
(Data FOR Datavalue IN
(col1, col2, col3, col4)
)AS unpvt
) AS x
GROUP BY ID
GO

The output will be :
-------------
ID MaxValue
----- ------------
1 500
2 600
3 800

No comments: