question

SimplyLukas avatar image
SimplyLukas asked

Splitting Concatenated FOR in SQL PIVOT

Hi All, I have this query below using Dynamic SQL to create new columns (by using @PivotColumns which puts all months between highest and lowest day) and pivot them out, however `[MonthList]` field is concatenated and would require to split it somehow as currently when i use it in FOR statement, it puts `[Monthly Rate]` values only if `[MonthList] = @PivotColumns` name, which is only when its 1 month (like Jun'17), but when theres more than 1 month (like Nov'16,Dec'16) then it doesnt show it under there. Could someone help with coding or advise how to approach this please? Example of Table (red is what it is not showing, but would need to show): ![alt text][1] Current Code: Set @SQLQuery = N'SELECT * FROM [dbo].[TableA] PIVOT( sum([Monthly Rate]) FOR [MonthsList] IN (' + @PivotColumns + ')) as P' EXEC sp_executesql @SQLQuery [1]: /storage/temp/4378-table-example.jpg
sql-server-2008dynamic-sql
table-example.jpg (28.6 KiB)
1 comment
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Rather than using PIVOT, I would use the Cross-Tab type query. See http://www.sqlservercentral.com/articles/T-SQL/63681/ If it needs to be dynamic (ie not hard-coded), that's a bit more complicated, as you'll need to also use a splitter function (eg http://www.sqlservercentral.com/articles/Tally+Table/72993/) but still easier than dynamic PIVOT statement. http://www.sqlservercentral.com/articles/T-SQL/63681/ I seem to be mostly pimping @Jeff Moden's stuff here. Which isn't a bad thing...
0 Likes 0 ·

0 Answers

·

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.