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