Sub-Select Concatenation - 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 2000 Forums

SQL Server Development (2000)

Sub-Select Concatenation

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-01-10 : 13:13:51

I've checked out the CSV articles on the site, but am having a bit of a problem. I need to concatenate the available options for each user of a list, so I cannot retrieve the information prior to the query, and then just insert it into the Select statement. I would like to do this in some fashion other than resorting to cursors, but that is all that I'm seeing right now. The below example does not work, but illustrates what I am trying to do I think.


Create Table tblUsers (
UserID int Not Null Identity(1,1),
Username varchar(25) Not Null
)

Create Table tblUserOptions (
UserID int Not Null,
OptionID int Not Null
)

Declare @OptionsList varchar(100)

Select
	UserID,
	OptionsList = (
		Select 
			@OptionsList = Coalesce(@OptionsList + ', ', '') + OptionID
		From
			tblUserOptions
		Order By
			OptionID
	)
From
	tblUsers
Order By
	Username

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-01-10 : 13:27:19

http://www.sqlteam.com/item.asp?ItemID=11021 didnt' help out at all. You should be able to join your two tables together to get your data into their same format and then work with the set based code there.

Give me a sec, and I will try to write up the actual code.

Got SQL?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-10 : 13:31:33

The comments by byrmol in the comments section of that article shows a user-defined function that will do the trick:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

- Jeff

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-01-10 : 13:43:03

I agree with jsmith. That is probably your best bet if you have the ability to create the user defined function.

otherwise:


declare @users table (userid int, username varchar(128))
declare @UserOptions table (userid int, optionID int)
insert into @users 
select 1, 'blastrix' union
select 2, 'yakoo'

insert into @UserOptions
select 1, 1 union
select 1, 2 union
select 1, 3 union
select 2, 1 union
select 2, 3 union
select 2, 5

declare @workingtable table (userid int, options varchar(100))

insert into @workingtable (userid, options)
SELECT u.userid, uo.optionID FROM @users u
	INNER JOIN @UserOptions uo
		ON (u.userid = uo.userid)
ORDER BY u.userid, uo.optionid

declare
    @list varchar(8000),
    @last int

select
    @list = '',
    @last = -1

update
    @workingtable
set
    @list = options = case
                       when @last <> userid then options
                       else @list + ', ' + options
                   end,
    @last = userid



select w1.userid, w1.options from @workingtable w1

you will then need to select the column with the greatest options length

Got SQL?

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-01-10 : 14:08:49

Thanks guys, that did the trick. I ended up using Byrmol's solution, so far it's working great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources