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 |
GoldenJet
Starting Member
1 Post |
Posted - 2014-01-13 : 14:20:14
|
I have a dataset being sent to me in the following columns:metric_namearea_nameMTD_dataQTD_dataYTD_dataThe metric name could have 1-x rows based on how many areas are returned for that metric (Boston, Chicago, Detroit, New York, etc):metric_name area_name MTD QTD YTDjobs_completed Boston 8 20 50jobs_completed Chicago 7 18 45jobs_completed Detroit 6 18 45jobs_completed Tampa 4 15 35What I need to do is pivot this table so I create a single row per metric_name, with columns for each area_name denoting the area and timeframe measure:metric_name (jobs_completed)boston_mtdbosont_qtdboston_ytdchicago_mtdchicago_qtdThe issue I'm running into is the number of areas and their values can always change, so I can't predefine a column value in a PIVOT function. I currently pivot this dataset in my front end code, but it understandably takes a bit to do so (create a hash map, then check values, etc).Is there a way to do this dynamic pivot in T-SQL?Thanks for any and all help you can provide! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|