concate comments from different rows.

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)

concate comments from different rows.

Author

Topic

terrence_chan99
Starting Member

12 Posts

Posted - 2007-01-08 : 17:36:50

Here is my example.

data
ID CommentID Comments
1 1 'app'
1 2 'le'
2 1 'or'
2 2 'an'
2 3 'ge'
3 1 'banana'

results want to get
1 apple
2 orange
3 banana

I was thinking to using the PIVOT, but as the number of row is unknown, I think it can't be use.

any help is appreciate!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:48:15

[code]-- prepare test data
declare @t table (id tinyint, commentid tinyint, comments varchar(6))

insert @t
select 1, 1, 'app' union all
select 1, 2, 'le' union all
select 2, 1, 'or' union all
select 2, 2, 'an' union all
select 2, 3, 'ge' union all
select 3, 1, 'banana'

-- show the data
select id,
max(case when commentid = 1 then comments else '' end) +
max(case when commentid = 2 then comments else '' end) +
max(case when commentid = 3 then comments else '' end)
from @t
group by id
order by id[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:49:38

Since SQL Server is rowbased, the number of rows does not matter!
If you are referring to the number of columns, just add more MAX(CASE rows !
If commentid is not found, an empty string is added to the string. If you think 20 is max, add 20 rows.

Peter Larsson
Helsingborg, Sweden

terrence_chan99
Starting Member

12 Posts

Posted - 2007-01-08 : 18:01:40

Peso,

Thanks for the reply. It works. however, the drawback is the limit of comment.

Let say you add 20 max statement, it will not work from 21. If I add more max statement, it will create a lot of empty string.

Is there a way to handle any number of comments?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:34:25

Yes. By using dynamic SQL with crosstab/pivot table.
Or, using an UDF.

CREATE FUNCTION dbo.fnConc
(
	@ID INT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	declare @s varchar(8000)

	select		@s = isnull(@s + comments, comments)
	from		test
	where		id = @ID
	order by	commentid

	return @s
end

Then all the rest is a breaze

-- prepare test data
create table test (id tinyint, commentid tinyint, comments varchar(6))

insert	test
select	1, 1, 'app' union all
select	1, 2, 'le' union all
select	2, 1, 'or' union all
select	2, 2, 'an'  union all
select	2, 3, 'ge' union all
select	3, 1, 'banana'

-- show the data
select distinct	id,
		dbo.fnConc(id) as comment
from		test
order by	id

drop table test

Please remember that there is a limit of 8000 chars when concating string this way.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-08 : 18:42:05

Here are some sql2005 ways too:

create table #x(ID int, CommentID int, Comments varchar(50))
insert #x select 1, 1, 'app'
union all select 1, 2, 'le'
union all select 2, 1, 'or'
union all select 2, 2, 'an'
union all select 2, 3, 'ge'
union all select 3, 1, 'banana'
union all select 3, 3, '2banana'


select	ID
	,(select Comments as [text()] from #x x2 where x1.ID = x2.ID order by x2.CommentID for xml path('')) as txt
from	#x x1
group by ID


select	ID
	,txt
from	#x x1
	cross apply(select Comments as [text()] from #x x2 where x1.ID = x2.ID order by x2.CommentID for xml path(''))x(txt)
group by ID,txt


-- the ordercte is just to get rid of any possible gaps in CommentID seq.
;with ordercte(ID, CommentID, Comments)
as
(
	select ID, rank() over(partition by ID order by CommentID) as CommentID, Comments
	from #x
)
,recursivecte(ID, CommentID, Comments)
as
(
	select ID, CommentID, cast(Comments as varchar(max))
	from ordercte
	where CommentID = 1
	union all
	select ordercte.ID, ordercte.CommentID, cast(recursivecte.Comments+ordercte.Comments as varchar(max))
	from ordercte join recursivecte
	on ordercte.ID = recursivecte.ID
	and ordercte.CommentID = recursivecte.CommentID+1
)
select ID, Comments
from
(
	select *,rank() over(partition by ID order by CommentID desc) as lst
	from recursivecte
)  x
where lst=1

select * from #x
drop table #x