Need assistance with SQL Pivot

• snavesavage

  Newbie

  Points: 3

  More actions

  January 28, 2015 at 1:43 pm

  #397937

  I have a table that looks like this

  SID name data

  1 Name Joe

  1 Email Address Joe.Doe@somewhere.com

  1 Comment Test

  2 Name SomeoneElse

  2 Email Address SomoneElse@somewhereelse.com

  2 Comment Another Test

  I would like to pivot this so the result will look like this:

  Name Email Comment

  Joe Joe.Doe@somewhere.com test

  SomeoneElse SomeoneElse@somewhereelse.com Another Test

  Thanks in advance!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 28, 2015 at 3:02 pm

  #1773827

  Something like this?

  SELECT

  MAX(CASE WHEN name ='Name' THEN data ELSE NULL END) AS Name,

  MAX(CASE WHEN name ='Email' THEN data ELSE NULL END) AS Email,

  MAX(CASE WHEN name ='Comment' THEN data ELSE NULL END) AS Comment

  FROM table

  GROUP BY SID

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Xedni

  SSCertifiable

  Points: 6951

  More actions

  January 28, 2015 at 8:56 pm

  #1773873

  Or if you prefer the pivot syntax (which lets face it, nobody does)....

  ;with t (SID, _Name, Data) as

  (

  select 1, 'Name', 'Joe' union all

  select 1, 'Email Address', 'Joe.Doe@somewhere.com' union all

  select 1, 'Comment', 'Test' union all

  select 2, 'Name', 'SomeoneElse' union all

  select 2, 'Email Address', 'SomeoneElse@somewhere.com' union all

  select 2, 'Comment', 'Another Test'

  )

  select *

  from t

  pivot (max(Data) for _Name in (Name, [Email Address], Comment)) p

  Executive Junior Cowboy Developer, Esq.[/url]