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
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
22 People are following this question.
