Creating a pivot like table result

• mike 57299

  SSCrazy

  Points: 2529

  More actions

  May 20, 2015 at 5:38 pm

  #318445

  I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:

  2013 2014 2015

  cnt1 cnt2 cnt1 cnt2 cnt1 cnt2

  jan 5 77 77 8 88

  etc....

  Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.

  Any ideas beside just looping through the data?

  Thanks,

  Mike

  create table #raw1 ([yr] int, [mnth] int, [cnt1] int)

  insert into #raw1 values

  (2013, 1, 5)

  , (2013, 2, 5)

  , (2013, 3, 5)

  , (2013, 4, 5)

  , (2013, 5, 5)

  , (2013, 6, 5)

  , (2013, 8, 5)

  , (2013, 9, 5)

  , (2013, 10, 5)

  , (2013, 11, 5)

  , (2013, 12, 5)

  , (2014, 1, 0)

  , (2014, 2, 5)

  , (2014, 3, 5)

  , (2014, 4, 5)

  , (2014, 5, 5)

  , (2014, 6, 5)

  , (2014, 8, 5)

  , (2014, 9, 5)

  , (2014, 10, 5)

  , (2014, 11, 5)

  , (2014, 12, 5)

  , (2014, 1, 5)

  , (2015, 2, 8)

  , (2015, 3, 8)

  , (2015, 4, 8)

  , (2015, 5, 8)

  create table #raw2 ([yr] int, [mnth] int, [cnt2] int)

  insert into #raw2 values

  (2014, 7, 77)

  , (2014, 8, 77)

  , (2014, 9, 77)

  , (2014, 10, 77)

  , (2015, 1, 88)

  , (2015, 2, 88)

  select * from #raw1

  select * from #raw2

  drop table #raw1

  drop table #raw2

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  May 20, 2015 at 6:05 pm

  #1799519

  I'm thinking that this should do the trick:

  WITH cntByYrMnth AS

  (

  SELECT

  yr = ISNULL(r1.yr, r2.yr),

  mnth = ISNULL(r1.mnth, r2.mnth),

  cnt1 = ISNULL(r1.cnt1,0),

  cnt2 = ISNULL(r2.cnt2,0)

  FROM #raw1 r1

  FULL JOIN #raw2 r2

  ON r1.yr = r2.yr AND r1.mnth = r2.mnth

  )

  SELECT

  mnth = LEFT(DATENAME(MONTH,CAST(mnth AS varchar(2))+'/1/2000'),3),

  --mnthnbr = mnth,

  [2013_cnt1] = SUM(CASE yr WHEN 2013 THEN cnt1 ELSE 0 END),

  [2013_cnt2] = SUM(CASE yr WHEN 2013 THEN cnt2 ELSE 0 END),

  [2014_cnt1] = SUM(CASE yr WHEN 2014 THEN cnt1 ELSE 0 END),

  [2014_cnt2] = SUM(CASE yr WHEN 2014 THEN cnt2 ELSE 0 END),

  [2015_cnt1] = SUM(CASE yr WHEN 2015 THEN cnt1 ELSE 0 END),

  [2015_cnt2] = SUM(CASE yr WHEN 2015 THEN cnt2 ELSE 0 END)

  FROM cntByYrMnth

  GROUP BY mnth;

  Edit: added a row to show how to get the first three letters of a month if you need jan, feb, mar, etc...

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001