Thinking outside the box

# 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