# Thinking outside the box

Patron Saint of Lost Yaks

## Easy sorted numeric pivoting with maximum three columns

-- Prepare sample data
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

-- 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

Print | posted on Tuesday, November 25, 2008 3:58 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## #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
8/10/2010 9:11 AM | dineshrajan
Comments have been closed on this topic.