Combine rows of SQL query - 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)

Combine rows of SQL query

Author

Topic

SangeethaMathew
Starting Member

1 Post

Posted - 2010-05-22 : 23:20:33

I have got a table that stores user's addresses.
One user can have 3 different addresses. Each address has a unique number.

"Select address from USER_ADDRESS where addrNum=300 or addrNum=301 or addrNum=302" would return three rows of addresses for a single user who has got address numbers 300,301 and 302 as follows:

address
-------
addr1
addr2
addr3

Is there any way to combine these results into a single row:

column1 column2 column3
------------------------------------------------------
addr1 addr2 addr3

Thanks and Regards,
Sangeetha

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-23 : 12:22:19

Yes with PIVOT or Cross-tab method. See booksonline for PIVOT function.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-24 : 07:33:57

select a.address1, b.address1, c.address1
from mytable a
left join mytable b on b.cust = a.cust and b.rectype = 302
left join mytable c on c.cust = a.cust and c.rectype = 303
where a.rectype = 301

all predicated on there being 1 "301" type record per customer to drive the rpcoess.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources