September 18, 2014 at 3:38 pm
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
September 18, 2014 at 3:47 pm
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply