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_name
area_name
MTD_data
QTD_data
YTD_data
The 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 YTD
jobs_completed Boston 8 20 50
jobs_completed Chicago 7 18 45
jobs_completed Detroit 6 18 45
jobs_completed Tampa 4 15 35
What 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_mtd
bosont_qtd
boston_ytd
chicago_mtd
chicago_qtd
The 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 |
|
|
|
|
|
Create pivot table on dynamic table