Comma Separated List

Question

Hello

I have code to generate a comma separated list but I need to include a group by.

    declare @tbl TABLE(
        WordID int IDENTITY(1, 1) NOT NULL,
        RuleNo int,
        PRIMARY KEY (WordID)
    )   

WordID        RuleNo
1                     1
2                     1
3                     1
4                     1
5                     1
6                     1
7                     1
8                     1
9                     1
10                   2
11                   2
12                   2
13                   2
14                   2

declare @tblWordsInRules table(
    WordID int IDENTITY(1,1) NOT NULL,
    Word varchar(50) NOT NULL,
(

WordID  Word
1             )
2             AND
3             blood
4             CONTAINS (
5             doctor
6             gp
7            OR
8             test
9            wait
10          waiting


    SELECT @list=COALESCE(@List + ',' + convert(varchar, xw.WordID), convert(varchar,xw.WordID))
    FROM         @tbl AS xt INNER JOIN
                          tblWordsInRules AS xw ON xt.Word = xw.Word
    order by xt.wordid

select @list

At the moment the above code produces a comma separated list like 4,8,2,9,2,10,7,3,1,4,5,7,6,1

What I would like is 2 records:
4,8,2,9,2,10,7,3,1     where RuleNo=1
4,5,7,6,1                   where RuleNo=2

In other words produce a comma separated list by grouping RuleNo.

Thanks

Answer 1

Your table schema seems to be missing some information about the column Word, but here is an example of putting column values to a delimited string. You should be able to modify this to your needs:

set nocount on  

 declare @tbl TABLE(
        WordID int NOT NULL,
        RuleNo int,
        PRIMARY KEY (WordID)
    )   

insert into @tbl select 1,                     1
insert into @tbl select 2,                     1
insert into @tbl select 3,                     1
insert into @tbl select 4,                     1
insert into @tbl select 5,                     1
insert into @tbl select 6,                     1
insert into @tbl select 7,                     1
insert into @tbl select 8,                     1
insert into @tbl select 9,                     1
insert into @tbl select 10,                   2
insert into @tbl select 11,                   2
insert into @tbl select 12,                   2
insert into @tbl select 13,                   2
insert into @tbl select 14,                   2

declare @tblWordsInRules table(
    WordID int  NOT NULL,
    Word varchar(50) NOT NULL
)

insert into @tblWordsInRules select 1,')'
insert into @tblWordsInRules select 2,'AND'
insert into @tblWordsInRules select 3,'blood'
insert into @tblWordsInRules select 4,'CONTAINS ('
insert into @tblWordsInRules select 5,'doctor'
insert into @tblWordsInRules select 6,'gp'
insert into @tblWordsInRules select 7  ,'OR'
insert into @tblWordsInRules select 8    ,'test'
insert into @tblWordsInRules select 9      ,'wait'
insert into @tblWordsInRules select 10       ,'waiting'
insert into @tblWordsInRules select 11       ,'list'

SELECT xt1.RuleNo, STUFF((SELECT ',' + cast(xt.WordID as varchar) FROM  @tbl AS xt INNER JOIN
                        @tblWordsInRules AS xw ON xt.WordID = xw.WordId
						where xt.RuleNo = xt1.RuleNo
						order by xt.wordid
						FOR XML PATH('')),1,1,'') AS words
from @tbl xt1
group by xt1.RuleNo
	

---

every day is a school day

Answer 2

Hi Naeem,

How about something like this:

;with w_cte(word, ruleNo, wordid, pass)
as
(
	select cast(word as varchar(50)), ruleNo, xt.wordid,1 
	from @tbl xt join @tblWordsInRules xw on xt.Wordid = xw.Wordid
	union all
	select cast(ct.word + ',' + t.word as varchar(50)), xt.ruleno, t.wordid,ct.pass+1
	from @tblWordsInRules t
	join @tbl xt on t.Wordid = xt.Wordid
	join w_cte ct on xt.ruleno = ct.ruleno
	and t.wordid > ct.wordid
)
select	c.word,
	c.ruleno
from	w_cte c
join	(select ruleno, max(pass) as [pass] from w_cte group by ruleno) m on c.pass = m.pass and c.ruleno = m.ruleno

Answer 3

You cannot use the variable assignment method to concatenate row data for multiple rows.  You will have to apply the logic to a scalar UDF or use the xml methods provided.  You can find more info on how to do this here http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and performance considerations here, http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html .

---

http://jahaines.blogspot.com/

Answer 4

That is absolutely fantastic - I had no idea you could use for xml path like that!

Answer 5

That is absolutely fantastic - I had no idea you could use for xml path like that!

The FOR XML will change the relational data to XML, and the ','  right after the SELECT will force the data to change to comma seperated. There is video for Plamen Ratchev on SQLshare.com you can watch.

---

Abdallah, PMP, MCTS

Answer 6

Thanks Adbshall - I understand for xml but had never considered using it in this manner.  Very creative.