Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

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

Powered by:
Powered By Subtext Powered By ASP.NET