SQL Query question - 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 2005 Forums

Transact-SQL (2005)

SQL Query question

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-04-28 : 13:57:08

Hi,

We have a table with 2 columns and following sample data:

COLA COLB
1 100
2 101
1 102
3 104
3 105
3 106
3 100

We need to get the output like:
Result

1 100|101
2 101
3 104|105|106|100

Currently we are using cursor for each COLA go over list of COLB values and add pipe to separate values, Can we do this in query without using cursor and loop.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-28 : 14:37:31

[code]SELECT COLA, (SELECT COLB+'|' FROM table WHERE COLA=D.COLA FOR XML PATH('')) AS List
FROM (SELECT DISTINCT COLA FROM table)D[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources