Pivoting Question

• charlesafarr

  SSC Veteran

  Points: 252

  More actions

  September 18, 2014 at 3:38 pm

  #296567

  Lets say you have address information (ie... Address, City, State, Zip) for one contact lets call him Bob.. So bob has two rows.

  example:

  Bob, 123 blah blah drive, City, state, zip

  Bob, 345 ugh ugh drive, City, state, zip

  How can i get the data to look like this:

  Bob, 123 blah blah drive, city state zip, 345 ugh ugh drive, city, state, zip

  Any ideas would be super helpful

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 18, 2014 at 3:47 pm

  #1746556

  I haven't tested the following code but it should give you an idea.

  With Rownums AS(

  SELECT *,

  ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ContactId) rn

  FROM Contacts

  )

  SELECT Name,

  MAX(CASE WHEN rn = 1 THEN address ELSE '' END) Address1,

  MAX(CASE WHEN rn = 1 THEN city ELSE '' END) City1,

  MAX(CASE WHEN rn = 1 THEN state ELSE '' END) State1,

  MAX(CASE WHEN rn = 2 THEN address ELSE '' END) Address2,

  MAX(CASE WHEN rn = 2 THEN city ELSE '' END) City2,

  MAX(CASE WHEN rn = 2 THEN state ELSE '' END) State2

  FROM Rownums

  GROUP BY Name

  For more information on this technique, read the following article: http://www.sqlservercentral.com/articles/T-SQL/63681/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2