question

happyappys avatar image
happyappys asked

Variable not Passing to Pivot Table in SQL

Hi I have a query that is basically selecting the next 40 years in the future from a list of people and dates and costs. The data is returned in column format however I need to PIVOT this data so that the years are across the top with the cost underneath. If I simply type a couple of years in the 'IN' statement like (IN ([2035],[2019],[2030],[2041])) then I get the correctly formatted results, the (no column name here also shows the results of the variable @col to confirm it is in the correct format that I typed the example dates as). ![alt text][1] I have managed to do the code and the @cols variable returns [2044],[2053],[2030],[2024],[2033],[2047],[2041],[2027],[2050],[2021],[2036],[2048],[2042],[2028],[2022],[2039],[2045],[2025],[2056],[2019],[2040],[2034],[2051],[2057],[2020],[2043],[2037],[2031],[2054],[2023],[2017],[2046],[2038],[2049],[2055],[2026],[2032],[2018],[2035],[2052],[2029] as I want it to, however when I use [@cols] in the 'IN' statement it just returns null values! Any ideas?? Thanks in advance! [alt text][2]. declare @cols nvarchar(max) with cte_data AS (SELECT DATEPART(YYYY,[next_date]) As YearDate FROM [ih].[dbo].[rm_loc_component_cycles] where [life_cycle] = 'REPL' and [next_date] > GetDate() and [next_date] < DATEADD(YYYY,40,GetDate()) and [total_cost] > 0 GROUP BY DATEPART(YYYY,[next_date]) ) select @cols = ( select STUFF( (SELECT ',' + '[' + CAST(YearDate AS VARCHAR(5)), ']' from cte_data FOR XML PATH('') ), 1, 1, '') ) SELECT * from (select [place_ref], CAST(DATEPART(YYYY,[next_date]) AS VARCHAR(5)) as YearDate, sum([total_cost]) as Cost from [ih].[dbo].[rm_loc_component_cycles] where [life_cycle] = 'REPL' and [next_date] > GetDate() and [next_date] < DATEADD(YYYY,40,GetDate()) and [total_cost] > 0 GROUP BY [place_ref],DATEPART(YYYY,[next_date]) ) AS x pivot ( sum(Cost) for YearDate IN ([@cols]) ) AS pvt ORDER BY [place_ref] [1]: /storage/temp/3935-cols.jpg [2]: /storage/temp/3934-cols2.jpg
sqlpivotvariables
cols2.jpg (48.1 KiB)
cols.jpg (72.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
One way of doing it is to prepare and then execute a dynamic select statement. In your case, after you declare and populate the @cols variable, comment out (or delete) your static select and replace it with this: declare @sql nvarchar(max); select @sql = N' SELECT * from ( select [place_ref], CAST(DATEPART(YYYY,[next_date]) AS VARCHAR(5)) as YearDate, sum([total_cost]) as Cost from [ih].[dbo].[rm_loc_component_cycles] where [life_cycle] = ''REPL'' and [next_date] > GetDate() and [next_date] < DATEADD(YYYY,40,GetDate()) and [total_cost] > 0 GROUP BY [place_ref],DATEPART(YYYY,[next_date]) ) AS x pivot (sum(Cost) for YearDate IN (' + @cols + ')) AS pvt ORDER BY [place_ref] '; exec sp_executesql @statement = @sql; go Basically, you populate the variable to store your combined statement and then execute it.Please note that in order to escape single quotes in your predicate, it is necessary to replace the single quote with 2 single quotes, i.e. 'REPL' needs to become ''REPL'' Hope this helps. Oleg
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank for resolution...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.