Multiple Rows into one Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple Rows into one Row

Author

Topic

thommes
Starting Member

1 Post

Posted - 2010-05-10 : 10:25:21

Searched around the web for an answer. Saw some ideas that involved cursors and temp tables which I'm not able to use. I need to create a single select statement to bring back one row of data for a result set that might contain 6 rows of data.

For example, lets say a table for contact information exists where a single customer could have 6 records: one each for residence address, mailing address, work address, residence phone, work phone and cell phone. I'd like the output row to be multiple columns:

resadd1 resadd2 resaddcity resaddstate mailadd1 mailadd2 mailaddcity mailaddstate workadd1 workadd2 workaddcity workaddstate resphone workphone cellphone

Not every customer will have every type of contact.

I have tried left outer joins and receive the results but it displays in multiple records instead of one record. Any help would be appreciated.

Thomas

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 11:19:47

Have a read about PIVOT in Books on line. If that doesn't get you out of trouble, then come back here for more help. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:46:00

or just use query like this


SELECT ContactName,
MAX(CASE WHEN AddressType='residence' THEN add1 ELSE NULL END) AS resadd1,
MAX(CASE WHEN AddressType='residence' THEN add2 ELSE NULL END) AS resadd2,
MAX(CASE WHEN AddressType='residence' THEN addcity ELSE NULL END) AS resaddcity,
MAX(CASE WHEN AddressType='residence' THEN addstate ELSE NULL END) AS resaddstate,
MAX(CASE WHEN AddressType='mail' THEN add1 ELSE NULL END) AS mailadd1,
MAX(CASE WHEN AddressType='mail' THEN add2 ELSE NULL END) AS mailadd2,
MAX(CASE WHEN AddressType='mail' THEN addcity ELSE NULL END) AS mailaddcity,
MAX(CASE WHEN AddressType='mail' THEN addstate ELSE NULL END) AS mailaddstate,
....
FROM Table
GROUP BY ContactName

because your columns are fixed always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources