sql query code dont run dynamic

Question

i want have the layout of pivot table with on top period YYYYMM in dynamic table. show sum of consumption per month show 1 year. but when i tried put the data (period (201801,201802...)) don't work with PERIOD table on dynamic ?!!

i don't know if im doing something wrong on the report ...can anyone help withit ?

The query without be in dynamic and work but when I tried to change to dynamic I can't make it work.

DECLARE @ColumnNames NVARCHAR(MAX) = ' '
DECLARE @SQL NVARCHAR (MAX) = ' '
SELECT   @ColumnNames += QUOTENAME(Period) + ','
FROM     [STOKVIS LIVE].[dbo].[SR_CONS_Consumption1year]
SET @ColumnNames = LEFT (@ColumnNames,LEN(@ColumnNames)-1) 
SET @SQL = 
SELECT [No_] ,[Group],[Lakeview],[Name],[class.],[Stock], [Period]
FROM [STOKVIS LIVE].[dbo].[SR_CONS_Consumption1year]
PIVOT (
    SUM ([Qty])
    FOR [Period]
    IN( ' + @ColumnNames +   ')
    )
    as pivortable

Dynamic SQL uses `exec sp_executesql` on SQL defined in a string. I'm not sure what your code is supposed to be doing. — Gordon Linoff, May 01 '18 at 10:46
i want the report show like : |item| group | name|Stock|201804|201803|201802|201801|…(the period should be dynamic — Tiago mota, May 01 '18 at 11:12

Justin · Answer 1 · 2018-05-01T13:27:27.157

0

You are executing 2 queries that require dynamic syntax - I've not tested but I think you could try something like this:

--Declare Variables
DECLARE @ColumnNames NVARCHAR(MAX) = NULL
DECLARE @SQL NVARCHAR(MAX) 

-- First dynamic query
SET @SQL = N'
         SELECT   @ColumnNames += QUOTENAME(Period) + '',''
         FROM     [STOKVIS LIVE].[dbo].[SR_CONS_Consumption1year] ;';
--Execute dynamic query
EXEC sp_executesql @sql, N'@ColumnNames NVARCHAR(MAX)', @ColumnNames;


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

--second dynamic query
SET @SQL = N'

    SELECT [No_] ,[Group],[Lakeview],[Name],[class.],[Stock], [Period]
    FROM [STOKVIS LIVE].[dbo].[SR_CONS_Consumption1year]
    PIVOT (
        SUM ([Qty])
        FOR [Period]
        IN( ' + @ColumnNames +   ')
        )
        AS PivotTable ;';

EXEC sp_executesql @sql, N'@ColumnNames NVARCHAR(MAX) OUT', @ColumnNames OUT;

SELECT @TransType

EDIT Added OUT in the past EXEC to identify they are output variables so you can use SELECT @TransType to get the result

edited May 01 '18 at 13:27

answered May 01 '18 at 11:53

Justin

954
4
22
44

i run the query and was succeeded in the run but don't show any data only come with : "Command(s) completed successfully." – Tiago mota May 01 '18 at 12:59
Hmm I'm wondering whether your first query is bringing back any columns you can try and execute the first part on it's own to see - if nothing is return then you need to figure out how to convert your columns into a list to use in the `IN CLAUSE` – Justin May 01 '18 at 13:25
this might set you in the right direction - https://stackoverflow.com/questions/18702827/sql-server-select-results-as-string-separated-with – Justin May 01 '18 at 13:36
Select * FROM (SELECT Period ;[No_] ,[Group] ,[Lakeview] ,[Name] ,[class.] ,[Stock] FROM [STOKVIS LIVE].[dbo].[SR_CONS_Consumption1year]) PIVOT ( SUM ([Qty]) FOR [Period] IN( [201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805],[201805], ) as pivortable @Justin – Tiago mota May 02 '18 at 15:14
when i run only show 1 month the report collect repeatedly the year for each item – Tiago mota May 02 '18 at 15:15

sql query code dont run dynamic

1 Answers1