asked
How to PIVOT Data across Multiple Columns with Grouping
Thanks in advance for any help you might be able to offer on the following problem. IF OBJECT_ID('tempdb..#Bicycle') IS NOT NULL DROP TABLE #Bicycle CREATE TABLE #Bicycle (Model NVARCHAR(50), Terrain NVARCHAR(20), TyrePressure INT NULL, TyreWidth INT NULL, SuspensionType NVARCHAR(20) NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK200','Road','100','25', NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK200','Track','120','28', NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK600','Road','140','25', 'Fixed') INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK600','Trail','110','25','Full') How can I display the data so it looks like: ![alt text][1] [1]: /storage/temp/4505-cuntitled.png I am able to achieve it using an SSRS report but I would like to have it as data returned from a query. I am ok with double barreling the column names if necessary, i.e. Road-Tyre Pressure, Road-Tyre Width, Road-Suspension, Track-Tyre Pressure, etc
@OTech What application is going to consume it and how will you present it? why are you not using SSRS? The best way to handle such situations is to do such stuff on application level. However, It is achievable to some extent in TSQL but there are some short comings e.g how would you want the nested grouping on columns in TSQL to be fed to the application? Anyhow, If you have limited set of Terrain types, then what usually is done at the back-end is to pivot/cross tab the data in TSQL. The following is a custom pivot/cross tab solution till the first level column grouping IF OBJECT_ID('tempdb..#Bicycle') IS NOT NULL DROP TABLE #Bicycle CREATE TABLE #Bicycle (Model NVARCHAR(50), Terrain NVARCHAR(20), TyrePressure INT NULL, TyreWidth INT NULL, SuspensionType NVARCHAR(20) NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK200','Road','100','25', NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK200','Track','120','28', NULL) INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK600','Road','140','25', 'Fixed') INSERT INTO #Bicycle (Model, Terrain, TyrePressure, TyreWidth, SuspensionType) VALUES ('AK600','Trail','110','25','Full') select model, coalesce(convert(varchar, MAX(case when Terrain = 'Road' then TyrePressure end)), '') TyrePressure, coalesce(convert(varchar, MAX(case when Terrain = 'Road' then TyreWidth end)), '') TyreWidth, coalesce(MAX(case when Terrain = 'Road' then SuspensionType end), '') Suspension, coalesce(convert(varchar, MAX(case when Terrain = 'Track' then TyrePressure end)), '') TyrePressure, coalesce(convert(varchar, MAX(case when Terrain = 'Track' then TyreWidth end)), '') TyreWidth, coalesce(MAX(case when Terrain = 'Track' then SuspensionType end), '') Suspension, coalesce(convert(varchar, MAX(case when Terrain = 'Trail' then TyrePressure end)), '') TyrePressure, coalesce(convert(varchar, MAX(case when Terrain = 'Trail' then TyreWidth end)), '') TyreWidth, coalesce(MAX(case when Terrain = 'Trail' then SuspensionType end), '') Suspension from #Bicycle group by model --Output Model TyrePressure TyreWidth Suspension TyrePressure TyreWidth Suspension TyrePressure TyreWidth Suspension AK200 100 25 120 28 AK600 140 25 Fixed 110 25 Full Otherwise, if the terrain column values can change or more values could be added in the future, then you can use the dynamic pivoting. You can visit the following wonderful blog by our very own Jeff Moden [Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs][1] [1]:
http://www.sqlservercentral.com/articles/Crosstab/65048/
18 People are following this question.
