SQLTeam.com | Weblogs | Forums

Help with PIVOT


#1

I've actually done one or two successful PIVOTs, but I do it so infrequently, that I always struggle with it. The script at the bottom creates a table of data that produces rows like

0 to 3 (14)	      10	50	0
4 to 6 (23)	      17.2	52	4
7 to 9 (5)	       3	65	7
'>9 (14)	      10	50	9

What I want is rows like below so I can graph the averages in Crystal Reports.

	0 to 3 (14)	        4 to 6 (23)	        7 to 9 (5)	       >9 (14)
AvgStart	10			17.2			3		10
AvgEnd	        50			52			65		50

One thing that could cause problems is the value in parentheses. In the case of '0 to 3' this is the number of people in that group. So while there will always be 4 groups that are 0 to 3, 4 to 6, 7 to 9, and >9, the way I build the data table, the 'labels' (column headings after the pivot) will change. I can remove it so the 4 groups are always the same, but the data is useful.

Greg

DROP TABLE WellnessReport

CREATE TABLE [dbo].[WellnessReport](
[GroupName] varchar NOT NULL,
[AvgStart] [float] NULL,
[AvgEnd] [float] NULL,
[GroupType] [int] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('0 to 3 (14)' ,10,50,0)

INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('4 to 6 (23)' ,17.2,52,4)

INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('7 to 9 (5)' ,3,65,7)

INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('>9 (14)' ,10,50,9)

SELECT * FROM [WellnessReport]


#2

For the value in parenthese, you can add this to the table [dbo].[WellnessReport] as a column like this

CREATE TABLE [dbo].[WellnessReport](
[GroupName] [varchar](50) NOT NULL,
[AvgStart] [float] NULL,
[AvgEnd] [float] NULL,
[GroupType] [int] NULL,
[NoRecords] [int] NULL
) ON [PRIMARY]

INSERT INTO [dbo].WellnessReport
VALUES ('0 to 3' ,10,50,0,14)
INSERT INTO [dbo].WellnessReport
VALUES ('4 to 6' ,17.2,52,4,23)
INSERT INTO [dbo].WellnessReport
VALUES ('7 to 9' ,3,65,7,5)
INSERT INTO [dbo].WellnessReport
VALUES ('>9' ,10,50,9,14)

Base on this , we can create, dynamically, the pivot.

DECLARE @nvcHeader AS NVARCHAR(500) 
    ,@nvcSQL AS NVARCHAR(MAX)

SELECT @nvcHeader = COALESCE(@nvcHeader +',','') 
            + '['+ [GroupName] +'] AS ' +  '[' +[GroupName] 
            + ' (' + CAST([NoRecords] AS NVARCHAR(30))+')'+']' 
    FROM dbo.[WellnessReport]
SET @nvcHeader ='col1,' + @nvcHeader
PRINT @nvcHeader

SET @nvcSQL = '
SELECT 
    ' + @nvcHeader + '    
FROM
(
    SELECT
        ''AvgStart'' AS col1
        ,P.[0 to 3]
        ,P.[4 to 6]
        ,P.[7 to 9]
        ,P.[>9]
    FROM
        (SELECT GroupName , AvgStart  FROM dbo.[WellnessReport]) AS cAS
        PIVOT (MIN(AvgStart) FOR [GroupName] IN ([0 to 3],[4 to 6],[7 to 9],[>9])
        ) P

    UNION ALL

    SELECT
        ''AvgEnd'' AS col1
        ,P.[0 to 3]
        ,P.[4 to 6]
        ,P.[7 to 9]
        ,P.[>9]
    FROM
        (SELECT GroupName , AvgEnd  FROM dbo.[WellnessReport]) AS cAE
        PIVOT (MIN(AvgEnd) FOR [GroupName] IN ([0 to 3],[4 to 6],[7 to 9],[>9])
        ) P
)A'

EXEC(@nvcSQL)

And the result is :

col1     0 to 3 (14)            4 to 6 (23)            7 to 9 (5)             >9 (14)
AvgStart 10                     17.2                   3                      10
AvgEnd   50                     52                     65                     50

Converting N rows to columns, including custom headers
#3

Nicely done. Thank you. I would have never come up with that.

Greg