How can I pivot results of query?

• NineIron

  SSChampion

  Points: 12536

  More actions

  December 2, 2014 at 8:06 am

  #292984

  How would you create a query to get the Metric as columns? Basically, to look like this.

  YearMonth BariatricSurgery BISurgeries EDTransfersToBI

  2014-10 100 10 50

  2014-11 50 30 20

  create table TEST

  (

  YearMonth varchar(7),

  Metric varchar(25),

  Data int

  )

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','BISurgeries',113)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','EDTransfersToBI',40)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','InpatientTransfersToBI',18)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','OPVisitsByBI',18)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','PercentAdmitsFromED',70)

  insert into TEST(YearMonth, Metric, Data) values( '2013-07','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','BISurgeries',18)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','EDTransfersToBI',32)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','InpatientTransfersToBI',24)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','OPVisitsByBI',9)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','PercentAdmitsFromED',64)

  insert into TEST(YearMonth, Metric, Data) values( '2013-08','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','BISurgeries',52)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','EDTransfersToBI',34)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','InpatientTransfersToBI',8)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','OPVisitsByBI',15)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','PercentAdmitsFromED',66)

  insert into TEST(YearMonth, Metric, Data) values( '2013-09','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','BISurgeries',18)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','EDTransfersToBI',33)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','InpatientTransfersToBI',15)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','OPVisitsByBI',11)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','PercentAdmitsFromED',68)

  insert into TEST(YearMonth, Metric, Data) values( '2013-10','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','BISurgeries',22)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','EDTransfersToBI',23)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','InpatientTransfersToBI',14)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','OPVisitsByBI',16)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','PercentAdmitsFromED',63)

  insert into TEST(YearMonth, Metric, Data) values( '2013-11','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','BISurgeries',12)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','EDTransfersToBI',33)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','InpatientTransfersToBI',25)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','OPVisitsByBI',16)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','PercentAdmitsFromED',70)

  insert into TEST(YearMonth, Metric, Data) values( '2013-12','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','BISurgeries',29)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','EDTransfersToBI',27)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','InpatientTransfersToBI',19)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','OPVisitsByBI',21)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','PercentAdmitsFromED',68)

  insert into TEST(YearMonth, Metric, Data) values( '2014-01','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','BISurgeries',38)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','EDTransfersToBI',17)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','InpatientTransfersToBI',11)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','OPVisitsByBI',22)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','PercentAdmitsFromED',67)

  insert into TEST(YearMonth, Metric, Data) values( '2014-02','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','BISurgeries',31)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','EDTransfersToBI',29)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','InpatientTransfersToBI',13)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','OPVisitsByBI',27)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','PercentAdmitsFromED',70)

  insert into TEST(YearMonth, Metric, Data) values( '2014-03','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','BISurgeries',30)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','EDTransfersToBI',29)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','InpatientTransfersToBI',14)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','OPVisitsByBI',32)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','PercentAdmitsFromED',66)

  insert into TEST(YearMonth, Metric, Data) values( '2014-04','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','BISurgeries',49)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','EDTransfersToBI',35)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','InpatientTransfersToBI',23)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','OPVisitsByBI',21)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','PercentAdmitsFromED',70)

  insert into TEST(YearMonth, Metric, Data) values( '2014-05','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','BISurgeries',56)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','EDTransfersToBI',40)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','InpatientTransfersToBI',12)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','OPVisitsByBI',38)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','PercentAdmitsFromED',72)

  insert into TEST(YearMonth, Metric, Data) values( '2014-06','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','BISurgeries',20)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','EDTransfersToBI',33)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','InpatientTransfersToBI',16)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','OPVisitsByBI',31)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','PercentAdmitsFromED',70)

  insert into TEST(YearMonth, Metric, Data) values( '2014-07','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','BISurgeries',16)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','EDTransfersToBI',42)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','InpatientTransfersToBI',21)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','OPVisitsByBI',18)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','PercentAdmitsFromED',69)

  insert into TEST(YearMonth, Metric, Data) values( '2014-08','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','BISurgeries',35)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','EDTransfersToBI',37)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','InpatientTransfersToBI',15)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','OPVisitsByBI',35)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','PercentAdmitsFromED',72)

  insert into TEST(YearMonth, Metric, Data) values( '2014-09','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','BISurgeries',26)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','EDTransfersToBI',41)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','InpatientTransfersToBI',16)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','OPVisitsByBI',27)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','PercentAdmitsFromED',72)

  insert into TEST(YearMonth, Metric, Data) values( '2014-10','ThoracicSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','BariatricSurgery',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','BISurgeries',20)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','EDTransfersToBI',35)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','GYNOncProcedures',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','GYNOncVisits',0)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','InpatientTransfersToBI',9)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','OPVisitsByBI',14)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','PercentAdmitsFromED',71)

  insert into TEST(YearMonth, Metric, Data) values( '2014-11','ThoracicSurgery',0)

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  December 2, 2014 at 8:32 am

  #1762111

  You need a dynamic PIVOT.

  Looks like this does the trick:

  DECLARE @sql nvarchar(max)

  SELECT @sql = STUFF((

  SELECT DISTINCT ',' + QUOTENAME(Metric)

  FROM TEST

  FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,1,SPACE(0))

  SET @sql = '

  SELECT *

  FROM TEST

  PIVOT (SUM(Data) FOR Metric IN ('+ @sql +')) AS pvt'

  EXEC(@sql)

  -- Gianluca Sartori

• NineIron

  SSChampion

  Points: 12536

  More actions

  December 2, 2014 at 8:41 am

  #1762115

  Wonderful. Thanx.

• Neil Burton

  SSC-Insane

  Points: 22999

  More actions

  December 2, 2014 at 8:44 am

  #1762120

  select yearMonth

  ,BariatricSurgery

  ,BISurgeries

  ,EDTransfersToBI

  ,GYNOncProcedures

  ,GYNOncVisits

  ,InpatientTransfersToBI

  ,OPVisitsByBI

  ,PercentAdmitsFromED

  ,ThoracicSurgery

  from

  (

  select

  YearMonth

  ,metric

  ,data

  from

  dbo.test t

  ) p

  pivot

  (

  max(data)

  for metric

  in (

  BariatricSurgery

  ,BISurgeries

  ,EDTransfersToBI

  ,GYNOncProcedures

  ,GYNOncVisits

  ,InpatientTransfersToBI

  ,OPVisitsByBI

  ,PercentAdmitsFromED

  ,ThoracicSurgery

  )

  ) pvt

  A common or garden pivot will do the same too. As a genuine question on my part, why would a dynamic pivot be the first choice here?

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  December 2, 2014 at 8:53 am

  #1762125

  BWFC (12/2/2014)

  ---

  As a genuine question on my part, why would a dynamic pivot be the first choice here?

  Not sure it's the first choice, but I assumed the list of columns to pivot was longer in the actual data.

  Yes, you're right: if the column list is known upfront, there's no need to use dynamic pivot.

  -- Gianluca Sartori

• Neil Burton

  SSC-Insane

  Points: 22999

  More actions

  December 2, 2014 at 8:57 am

  #1762127

  Fair enough, I was just wondering if'd missed something 🙂 I think that QUOTENAME is going to be something that will make my life easier though.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537