0

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the query with dynamic sql it breaks. Here is the statement that works fine.

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(record_name) 
            FROM ams_data
            WHERE report_name = 'class_and_component_prices'
            AND commodity ='whey'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        record_name,
        value,
        date,
        commodity
    FROM 
       ams_data
        WHERE report_name = ''class_and_component_prices''
        AND commodity = ''whey''
) t 
PIVOT(
    max(value)  
    FOR record_name IN ('+ @cols +')
)  AS pivot_table ;';

EXECUTE sp.executesql @sql

And here is my version that takes parameters.

ALTER PROCEDURE  [dbo].[pivot_record]
(
    -- Add the parameters for the stored procedure here
    @tablename VARCHAR(max) = nass_data,
    @report_name VARCHAR(max) = class_and_component_prices,
    @commodity VARCHAR(max) = whey
)
AS
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
        DECLARE @cols AS NVARCHAR(MAX),
                @sql  AS NVARCHAR(MAX),
                @actualTable AS VARCHAR(MAX)

        SELECT @actualTable = QUOTENAME( TABLE_NAME )
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = @tablename

        SET @sql = '
        STUFF((
            SELECT distinct '','' + QUOTENAME(record_name) 
            FROM ' + @actualTable + ' 
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + ''' 
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
            ,1,1,'''')'        

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

        -- construct dynamic SQL
        SET @sql ='
        SELECT * FROM   
        (
            SELECT 
                record_name,
                value,
                date,
                commodity

            FROM 
               ' + @tablename + '
                WHERE report_name = ' + @report_name + '
                AND commodity = ' + @commodity + '
        ) t 
        PIVOT(
            max(value)  
            FOR record_name IN ('+ @cols +')
        )  AS pivot_table ;';

        -- execute the dynamic SQL
        EXECUTE sp_executesql @sql;

When I attempt to run this, I get an error from the first sql statement. I printed off the sql statement prior to attempting to execute and store the result in @cols to ensure that it is exactly the same as my first statement that worked.

error message

I am assuming that some quirk with dynamic sql is causing the issue, but assistance would be appreciated.

EDIT: I figured out that I needed to include a SELECT before the STUFF clause, but I still do not get my intended result, now it just outputs an empty table instead of the proper pivoted data.

2
  • Take a look at here, with samples using dynamic columns. Someone even wrote a proc to conver stackoverflow.com/questions/15931607/… – Jerry Hung Jun 24 '20 at 3:45
  • Yes, I followed pretty much the exact same procedure, the issue was when I tried to parametrize the statement that generates the list of distinct values within the column, it broke. My first code block is essentially what they have and it works fine. – Chives Jun 24 '20 at 3:47
0

It's hard to debug without seeing the output of your 2nd @SQL or @cols. I'd start from there to debug, SELECT/PRINT the variable and copy-and-paste-run-it

or try different way of cols?

DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

FYI this is my PIVOT query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = '
select *
from 
(
  select SQLInstanceName, Date, node_name
  from #Final
  WHERE 1=1 AND  Has_Changed = ''Y''
) src
pivot
(
  MIN(node_name)
  for [Date] in ('  + @ColumnName + ')
) piv
order by 1
'
--SELECT @DynamicPivotQuery --debug
EXEC sp_executesql @DynamicPivotQuery

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.