Stephan replied on 10-Nov-06 11:18 AM
Hi,

Is there a way to concatenate strings using group by? Like a SUM for
strings...

For example, I have:

userId   string
1          A
1          B
2          C

I would like to group by userId to obtain this:

userId   string
1          A B
2          C

I'm currently using a UDF like this:

select userId, dbo.concactString(userId) from tbl group by userId,
dbo.concactString(userId)

Which is very slow...

Is that possible whitout using a slow user defined function?

I'm using SQL 2005

Thanks,

Stephane




Razvan Socol replied on 10-Nov-06 11:37 AM
Hi, Stephane

See:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Razvan
Stephan replied on 10-Nov-06 11:48 AM
Thanks. I saw that but it what still slow.

Here's something pretty fast!

SELECT
count(1) as nbCount,
phrase = RTRIM(REPLACE(REPLACE(o.list,'<word>',''),'</word>',' '))
FROM
tbl_Words c
CROSS APPLY
(
SELECT
word
FROM
tbl_Words s
WHERE
s.userid = c.useriid
order by id
FOR XML PATH('')
) o (list)
group by o.list
order by nbcount desc
Anith Sen replied on 10-Nov-06 11:51 AM
See: http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith
Razvan Socol replied on 10-Nov-06 11:58 AM
That method is also in the article I quoted.

Razvan
SQL Server - Concatenate string using Group By
Asked By Vadim Tropashko on 10-Nov-06 02:43 PM
It is quite ironic that Sybase has a List aggregate function, and it's
sucessor does not. With List aggregate the syntax is the most
straightforward:

select deptno, list(ename||', ')
from emp
group by deptno

In SQL 2005 you can leverage recursive with clause:

with emp_lists (deptno, list, postfix, length) as
( select distinct deptno, '', '', 0
from emp
union all
select e.deptno, list || ', ' || ename, ename, length+1
from emp_lists el, emp e
where el.deptno = e.deptno
and e.ename > el.postfix
)
select deptno, list from emp_lists e
where length = (select max(length)
from emp_lists ee
where e.deptno = ee.deptno)