CODEWITHSUNDEEP
functionsql-server-2005aggregate
liudaisuda
liudaisuda

Reputation: 59

sql server function on a group of records

I have a group by query. Is there a way to access all records within a group for each every group in pure T-SQL 2005?

What I want to achieve if that, say I have a table Table(A, B) and a group by A query. Then I need to get a list of concatenations, each of which is a concatenation of the values of B within a group. Any thought?

Upvotes: 0

Views: 13

Answers (1)

Yuri Bulgakov
Yuri Bulgakov

Reputation: 36

You can do something like this:

create table T(a char(1), b char(1))

insert into T values ('a','b'),('a','c'),('a','d'),('e','f'),('e','g');

select distinct a
    ,concat_b = (select b from T t2 where t2.a=t1.a for xml path(''))
from T t1

Column "concat_b" will contain XML string like "<b>b</b><b>c</b><b>d</b>", you can then replace XML tags with delimiters that you want.

Upvotes: 2

Related Questions