help with query - select multiple rows as 1 column

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)

help with query - select multiple rows as 1 column

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 14:47:36

Hi,

I have the query below, which I would like to bring back as 1 column, with a comma delimiter.

Is there an efficient way to do this ? I have never attempted nor do I think I have seen it done before.

Thanks for any help!
mike123

SELECT ID.interest FROM tblinterests I
JOIN tblInterestDetails ID on ID.interestID = I.interestID

CREATE TABLE [dbo].[tblInterestDetails](
[interestID] [int] IDENTITY(1,1) NOT NULL,
[interest] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblInterests](
[userID] [int] NOT NULL,
[interestID] [int] NOT NULL
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:50:24

you mean for each userid or everything as a single value?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 14:54:09

if for each userid use this

SELECT i.UserID,LEFT(il.intlist,LEN(il.intlist)-1)
FROM tblInterests i
CROSS APPLY (SELECT interest + ','
             FROM tblInterestDetails
             WHERE interestID=i.interestID
             FOR XML PATH(''))il(intlist)

if everything as whole use this

SELECT LEFT(il.intlist,LEN(il.intlist)-1)
FROM (SELECT interest + ','
             FROM tblInterestDetails
             FOR XML PATH(''))il(intlist)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 15:03:48

Hi Visakh16,

I am looking to just select the "ID.interest" value on 1 row, 1 column. Seperated by commas .. Trying your queries now :)

Thanks!
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 15:24:04

Hi Visakh16,

It looks like your second approach might be right. We have all the interests in 1 column and row, I just cant check if its the correct records. No reason to believe its not :) I did leave out something I thought would be easy to add in, but it appears its not so easy now.

I just want to add "WHERE userID = 500" to the query .. Can I do this ?

Thanks once agian! very much appreciated

mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 23:38:29

quote:
Originally posted by mike123

Hi Visakh16,

It looks like your second approach might be right. We have all the interests in 1 column and row, I just cant check if its the correct records. No reason to believe its not :) I did leave out something I thought would be easy to add in, but it appears its not so easy now.

I just want to add "WHERE userID = 500" to the query .. Can I do this ?

Thanks once agian! very much appreciated

mike123

SELECT LEFT(il.intlist,LEN(il.intlist)-1)
FROM (SELECT interest + ','
             FROM tblInterestDetails id
             JOIN tblInterests i
             ON i.interestID=id.interestID
             WHERE i.UserID=500
             FOR XML PATH(''))il(intlist)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-21 : 04:36:23

worked perfectly, thank you :) I should read up on "FOR XML PATH", looks interesting

cheers,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 05:01:06