SubQuery Concatenation in a select statement

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 2000 Forums

SQL Server Development (2000)

SubQuery Concatenation in a select statement

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2003-03-06 : 20:24:24

I was wondering if it was possible to concatenate a subquery so that it returns a comma-delimited value, inside a select statement.

For instance, I have table #1:



Users

UserID  UserName
1       Joe
2       Bob


Rights
UserID  RightID
1       1
1       2
1       3


The result set I want is:

UserID  UserName  Rights
1       Joe       1,2,3
2       Bob       NULL

I tried making a user defined function that would grab the subquery then concatenate it, but since i can't use the exec statement, the select statement would have to be static ... i'd like to be able to change the criteria on the rights table.

thanks for the help in advance.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-06 : 21:10:20

Take a look at this article.
http://www.sqlteam.com/item.asp?ItemID=11021

The page47 table in your case would be Users inner joined with Rights.

If you take a look at the article comments you will find a SQL SERVER 2000 UDF solution by byrmol.

Edited by - ValterBorges on 03/06/2003 21:12:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources