x

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

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

cuntitled.png (5.9 kB)
more ▼

asked Feb 13 at 04:45 PM in Default

avatar image

OTech
1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@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

more ▼

answered Feb 13 at 06:15 PM

avatar image

Usman Butt
14.3k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x447
x118

asked: Feb 13 at 04:45 PM

Seen: 29 times

Last Updated: Feb 13 at 06:15 PM

Copyright 2018 Redgate Software. Privacy Policy