sub query and returning comma delimited results

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 query and returning comma delimited results

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2003-11-16 : 21:34:17

Hi there,

is it possible to have a query that returns multiple rows but put these rows into a comma delimited column? for example

SELECT M.BUSINESSNAME, M.EMAIL, m.firstname + ' ' + m.surname as CONTACTNAME,
(SELECT KEYWORDS FROM TBLKEYWORDS_JOIN_MEMBERS WHERE TBLKEYWORDS_JOIN_MEMBERS.DOCID = D.MEMBERDOCID) AS KEYWORDS
FROM tblMembers M INNER JOIN tblMemberDocs D ON D.MEMBERID = M.MEMBERID

I need to export the results to an excel file and dont want multiple records for each keyword associated to a member.

thanks - matt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-16 : 21:42:30

This should do it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978
http://www.sqlteam.com/item.asp?ItemID=11021

If you search the forums for "CSV function" you'll find a user-defined function that also will work. The function will let you do it in a subquery.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-16 : 21:45:16

You can create a function to return the string

create function getcsv
(
@id int
)
returns varchar(4000)
as
begin
declare @s varchar(4000)
select @s = coalesce(@s+',','') + KEYWORDS
FROM TBLKEYWORDS_JOIN_MEMBERS WHERE TBLKEYWORDS_JOIN_MEMBERS.DOCID = @id

return (@s)
end
go

SELECT M.BUSINESSNAME, M.EMAIL, m.firstname + ' ' + m.surname as CONTACTNAME,
dbo.getcsv(D.MEMBERDOCID) AS KEYWORDS
FROM tblMembers M INNER JOIN tblMemberDocs D ON D.MEMBERID = M.MEMBERID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources