Help with PIVOT

Transact-SQL
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
1 Like