Easy sorted numeric pivoting with maximum three columns

Tue Nov 25, 2008 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005, sql-server-2000

-- Prepare sample data

DECLARE @Sample TABLE

(

ID INT,

col INT

)

INSERT @Sample

SELECT 0, 1 UNION ALL

SELECT 0, 2 UNION ALL

SELECT 1, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 1, 3 UNION ALL

SELECT 2, 5 UNION ALL

SELECT 3, 6 UNION ALL

SELECT 5, 8 UNION ALL

SELECT 5, 9 UNION ALL

SELECT 4, 7

-- Pivot the source data

SELECT ID,

MIN(col) AS col1,

CASE COUNT(*)

WHEN 1 THEN NULL

WHEN 2 THEN MAX(col)

ELSE SUM(col) - MIN(col) - MAX(col)

END AS col2,

CASE COUNT(*)

WHEN 3 THEN MAX(col)

ELSE NULL

END AS col3

FROM @Sample

GROUP BY ID

ORDER BY ID

Legacy Comments

dineshrajan
2010-08-10

re: Easy sorted numeric pivoting with maximum three columns
Good Post.

My Another approach

DECLARE @Sample TABLE
(
ID INT,
col INT
)

INSERT @Sample
SELECT 0, 1 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 0, 2 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 5, 8 UNION ALL
SELECT 5, 9 UNION ALL
SELECT 4, 7

SELECT * FROM @Sample

SELECT Grp.ID,[Col1]=SUM(CASE WHEN ROWID=1 THEN S.col Else NULL END)
,[Col2]=SUM(CASE WHEN ROWID=2 THEN S.col Else NULL END)
,[Col3]=SUM(CASE WHEN ROWID=3 THEN S.col Else NULL END)
FROM
(
SELECT id
FROM @Sample s1
GROUP BY id
) Grp
CROSS JOIN
(
Select S.*,[RowID]=ROW_NUMBER() OVER (PARTITION BY S.ID ORDER BY ID)
FROM
@Sample S
) S
WHERE S.ID = Grp.ID
GROUP BY Grp.ID