Pivot table with Dynamic SQL for multiple columns

Question

New to SQL Pivot and Dynamic SQL. I have a table with a column that I would like to make the distinct attributes become column headings in a pivot table, while also creating correct major categories for an additional column about the main data, and report a score in those fields. Problem is I can't get the scores to populate correctly or the distinct column headings.

I can't get the Measure1 fields to become column headings and I can't get the scores to populate. With the correct [measure name] related to the Measure1 and associated score. Thanks for any help in advance.

Declare @ColumnNames NVARCHAR (MAX) = ''
Declare @SQL NVARCHAR (MAX)= ''

Select @ColumnNames += QUOTENAME([Measure1]) + ','
From [dbo].[Table1]

Group By [Measure1]

SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1)
SET @SQL =
'Select * FROM
(Select
    case
        When [measure1] like ''%lower%'' then ''Lower''
        When [measure1] like ''%upper%'' then ''Upper''
        When [measure1] like ''%Days%'' then ''Days''
        When [measure1] like ''%Occur%'' then ''Occurrences''
        When [measure1] like ''%Pred%'' then ''Predicted''
        When [measure1] like ''%Ratio%'' then ''Ratio''
        end as [Measure1]
    ,case
        When [Measure Name] like ''%Tewksbury%'' then ''Stan''
        When [Measure Name] like ''%Boston%'' then ''Steve''
        When [Measure Name] like ''%Seattle%'' then ''Payton''
        When [Measure Name] like ''%Denver%'' then ''Lavell''
        When [Measure Name] like ''%Houston%'' then ''Caren''
        When [Measure Name] like ''%Hudson%'' then ''Michael''
        end as Measure_Category
    ,[Main ID] AS [Cust ID]
    ,[Compared to Average]
    ,[score]

From
    [dbo].[Table1]
) AS BaseData

PIVOT (
    MAX([Compared to Average])
    FOR [measure1]
    IN (' + @ColumnNames + 

')) AS PivotTable'



Execute sp_executesql @SQL

Actual Results:

Desired Results:

Close up of Desired Results:

Could you please post what is the results you are getting and what you are expecting? — Fuzzy
– Fuzzy, Commented Jan 13, 2016 at 21:38

JamieD77 · Accepted Answer · 2016-01-13 22:14:42Z

Well your @ColumnNames param doesn't match what you're actually selecting in your pivot query, so you might want to change your @ColumnNames param to something like this instead.

SELECT  @ColumnNames = COALESCE(@ColumnNames + ',','') + QUOTENAME([Measure1])
FROM    (SELECT DISTINCT
                CASE WHEN [measure1] LIKE '%lower%' THEN 'Lower'
                     WHEN [measure1] LIKE '%upper%' THEN 'Upper'
                     WHEN [measure1] LIKE '%Days%' THEN 'Days'
                     WHEN [measure1] LIKE '%Occur%' THEN 'Occurrences'
                     WHEN [measure1] LIKE '%Pred%' THEN 'Predicted'
                     WHEN [measure1] LIKE '%Ratio%' THEN 'Ratio'
                END AS [Measure1]
         FROM   [dbo].[Table1]
        ) t
WHERE [Measure1] IS NOT NULL

and you can leave off this.

SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1)

Collectives™ on Stack Overflow

Pivot table with Dynamic SQL for multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related