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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create pivot table on dynamic table

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

Posted - 2014-01-14 : 04:20:31
see
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -