Combining column values into one 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

General SQL Server Forums

New to SQL Server Programming

Combining column values into one column

Author

Topic

BargainBasement
Starting Member

2 Posts

Posted - 2007-10-30 : 04:48:21

Hi Folks, Im new to SQL, and I am trying to do the following:

I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.

I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.

I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:

DECLARE @List varchar(100)

SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1

SELECT @List

I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 05:27:49

Refer
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-10-30 : 08:51:49

try this code it may help you

create table result_set ([path] varchar(25),[filename] varchar(25),keywords char(50))

declare concate_ext cursor scroll
for select * from
(select d.[path],d.[filename],k.keywordstring keyword from [document] d left outer join
documentkeyword dk on d.Docid = dk.docid
left outer join keyword k on k.kwdid=dk.kwdid) #temp

declare concate_int cursor scroll
for select * from
(select d.[path],d.[filename],k.keywordstring keyword from [document] d left outer join
documentkeyword dk on d.Docid = dk.docid
left outer join keyword k on k.kwdid=dk.kwdid) #temp

declare @var1 varchar(25)
declare @var2 varchar(25)
declare @var3 char(50)

open concate_ext
fetch first from concate_ext into @var1,@var2,@var3 --ABSOLUTE {n | @nvar}
while @@fetch_status=0
begin
open concate_int
declare @var11 varchar(25)
declare @var22 varchar(25)
declare @var33 char(50)
declare @flag int
set @flag=0
fetch first from concate_int into @var11,@var22,@var33
while @@fetch_Status=0
begin
if @flag=0
begin
set @flag=1
end
else
begin
if @var11=@var1 and @var22=@var2
begin
set @var3=@var3+','+@var33
end
end
fetch next from concate_int into @var11,@var22,@var33
end
insert into result_set values(@var1,@var2,@var3)
close concate_int
fetch next from concate_ext into @var1,@var2,@var3
end
close concate_ext
deallocate concate_int
deallocate concate_ext --,@count1,@count,@var1,@var2,@var3,@path,@filename,@keyword,@flag

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 09:28:38

Also make sure to read the link I posted

Madhivanan

Failing to plan is Planning to fail

BargainBasement
Starting Member

2 Posts

Posted - 2008-01-24 : 01:20:40

I am sorry, it has been some time since I received replies on this thread, I am just getting back to this project now. I wanted to thank madhivanan for the link, and particularly arorarahul.0688 for the great customized and instructive code sample.

I will try these methods ASAP. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources