Junar Aqui Jacob Junar Aqui Jacob - 3 months ago 18
SQL Question

Dynamic Pivot Sql Server

I got very simple query but I can't make it work.
Please help. thank you

i followed the tut but my query doesn't work

DECLARE @ColumnName NVARCHAR(MAX) = ''
DECLARE @Query NVARCHAR(MAX) = ''
DECLARE @Site NVARCHAR(MAX) = ''
DECLARE @Date NVARCHAR(MAX) = ''
DECLARE @MOnth NVARCHAR(MAX) = ''


SELECT @ColumnName += QUOTENAME([Product Category]) + ','
FROM
(
SELECT DISTINCT [Product Category]
FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE
WHERE [Customer No] = 'SLPIP' AND
DATEPART(yyyy, [Posting Date])= '2016' AND
CONVERT(CHAR(3), [Posting Date], 0)= 'Jan'
GROUP BY [Customer No], [Product Category]
) AS T1

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

set @Site = 'SLPIP'
set @Date = '2016'
set @Month = 'Jan'

SET @Query = 'SELECT * FROM
(
SELECT [Customer No], [Product Category]
FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE
WHERE [Customer No] = ' + @Site + ' AND
DATEPART(yyyy, [Posting Date])=' + @Date + ' AND
CONVERT(CHAR(3), [Posting Date], 0)=' + @Month + '
--GROUP BY [Customer No], [Product Category]
) T2
PIVOT (
COUNT([Customer No])
FOR [Product Category] IN (' + @ColumnName + ')
) T3'
--print @Query
EXEC sp_executesql @Query


Error

Msg 207, Level 16, State 1, Line 5
Invalid column name 'SLPIP'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Jan'.

Answer

First of all, try to use parameterized queries as much as possible and use string concatenation only when it's absolutely neccessery.

So by fixing that, your query should look somehow like that:

DECLARE @ColumnName NVARCHAR(MAX) = '';
DECLARE @Query NVARCHAR(MAX) = '';
DECLARE @Site NVARCHAR(MAX) = '';
DECLARE @Date NVARCHAR(MAX) = '';
DECLARE @MOnth NVARCHAR(MAX) = '';

SELECT @ColumnName += ',' + QUOTENAME([Product Category])
FROM (
    SELECT DISTINCT [Product Category]
    FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE
    WHERE [Customer No] = 'SLPIP'
        AND DATEPART(yyyy, [Posting Date]) = '2016'
        AND CONVERT(CHAR(3), [Posting Date], 0) = 'Jan'
    GROUP BY [Customer No], [Product Category]
    ) AS T1;

SET @Site = 'SLPIP';
SET @Date = '2016';
SET @Month = 'Jan';

SET @Query = '
    SELECT *
    FROM (
        SELECT [Customer No], [Product Category]
        FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE 
        WHERE [Customer No] = @Site
            AND DATEPART(yyyy, [Posting Date]) = @Date
            AND CONVERT(CHAR(3), [Posting Date], 0) = @Month
        ) T2
    PIVOT (
        COUNT([Customer No])
        FOR [Product Category] IN (' + STUFF(@ColumnName, 1, 1, '') + ')
        ) T3';

EXEC sp_executesql @Query
    , N'@Site NVARCHAR(MAX), @Date NVARCHAR(MAX), @Month NVARCHAR(MAX)'
    , @Site
    , @Date
    , @Month;

I would also encourage you to fix the way you query [Posting Date]

Instead of this:

AND DATEPART(yyyy, [Posting Date]) = '2016'
AND CONVERT(CHAR(3), [Posting Date], 0) = 'Jan'

I'd rewrite it like that:

AND [Posting Date] >= '2016-01-01'
AND [Posting Date] <  '2016-02-01';

This will use index on [Posting Date], if there's any.

Comments