Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2012-01-25 : 05:29:29
|
| Hi,The query below works fine.As you can see, pivot is used so that I get [CodeValue] fields as columns...Problem is that the underlying table in future may have other [CodeValue] such as:[CodeValue7] or [CodeValue8], etc...At present, to get this to cope with future data, I have to manually add the new [CodeValue] to the end of the existing sql.Question:Is there a way to make this addition of extra fields dynamic so that I do not have to manually add the new values to show as columns?So that the system would pickup the new values and show as columns?ThanksSELECT Field1Fixed, Field2Fixed, [CodeValue1], [CodeValue2], [CodeValue3], [CodeValue4], [CodeValue5], [CodeValue6]FROM (SELECT Field1Fixed, Code, Field2Fixed, Duration FROM tblMain WHERE DataValue = 'Books' AND Code IN ('CodeValue1', 'CodeValue2', 'CodeValue3', 'CodeValue4', 'CodeValue5', 'CodeValue6')) AS SourceTable PIVOT (SUM(Duration) FOR Code IN ([CodeValue1], [CodeValue2], [CodeValue3], [CodeValue4], [CodeValue5], [CodeValue6])) AS PivotTable |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-01-25 : 05:47:01
|
quote:
Originally posted by khtan
You have to form the pivot query using dynamic SQL.see http://www.sommarskog.se/dynamic_sql.html
KH[spoiler]Time is always against us[/spoiler]
Thank you |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
dynamic pivot