Brain freeze join issue.

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  September 11, 2012 at 3:47 am

  #281272

  What's the most elegant way to achieve the following? ...

  CREATE TABLE TabOne

  (db CHAR(1), type INT, size INT)

  INSERT INTO TabOne VALUES ('a', 1, 1)

  INSERT INTO TabOne VALUES ('a', 2, 2)

  INSERT INTO TabOne VALUES ('a', 2, 2)

  INSERT INTO TabOne VALUES ('b', 1, 3)

  INSERT INTO TabOne VALUES ('b', 1, 5)

  INSERT INTO TabOne VALUES ('b', 2, 4)

  INSERT INTO TabOne VALUES ('c', 1, 5)

  INSERT INTO TabOne VALUES ('c', 2, 6)

  INSERT INTO TabOne VALUES ('c', 3, 7)

  SELECT * FROM TabOne

  SELECT db, type, SUM(size) as SumSize

  FROM TabOne

  GROUP BY db, type

  ORDER BY 1,2

  dbtypeSumSize

  a11

  a24

  b18

  b24

  c15

  c26

  c37

  I want to have the following results ...

  db type1 SumSizeType1 type2 SumSizeType2 type3

  a 1 1 2 4 ...

  b ....

  Hope it's clear. Grateful for your time, as ever!

  🙂

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 11, 2012 at 4:12 am

  #1536111

  Looks like you are looking for "Dynamic Pivot":

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  September 11, 2012 at 4:44 am

  #1536140

  Perfect. Thanks Eugene.

  SELECT db,

  SUM(CASE WHEN type = 1 THEN size ELSE 0 END) AS [Type1],

  SUM(CASE WHEN type = 2 THEN size ELSE 0 END) AS [Type2],

  SUM(CASE WHEN type = 3 THEN size ELSE 0 END) AS [Type3],

  SUM (size) AS TotalSize

  FROM #TabOne

  GROUP BY db