CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60751

Concatenate and group multiple rows

I am doing this:

, cte_proc_code (accn,proc_code) as
(SELECT accn_id, 
    (SELECT proc_code + ','
     FROM [XDataFullExtract].[dbo].[accn_billed_procedures] 
     FOR XML PATH('')
    ) 
FROM [XDataFullExtract].[dbo].[accn_billed_procedures] 
group by accn_id)

My data looks like this:

accn_id,proc_code
AA123, 1132
AA123, 5234
AA123, 4524
BB123, 2345
BB123, 4444

The result that I would like is:

accn_id,proc_code
AA123, 1132, 5234, 4524
BB123, 2345, 4444

My solution works however IT'S WAY TOO SLOW!!

Is there a faster way to do this? I think the XML is slowing me down.

Upvotes: 0

Views: 2033

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

In the query you have provided it is not "the XML that is slowing you down".
You are building a comma separated string using all values in your table for every row returned.
You are missing a where clause in your sub-query that should filter your concatenated values to only use rows for the current row in the outer query.

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

This approach involves adding a staging column to your table, but could run faster:

-- table, with new varchar(max) column cncat added
declare @t table(accn_id varchar(30), proc_code varchar(30), cncat varchar(max));
declare @concat varchar(max)=''; --staging variable
 
insert into @t values
('AA123','1132','')
, ('AA123','5234','')
, ('AA123','4524','')
, ('BB123','2345','')
, ('BB123','4444','');
 
-- update cncat
with cte as (select *,r=row_number()over(partition by accn_id order by proc_code) from @t)
update cte set @concat = cncat = case cte.r when 1 then '' else @concat end + ','+proc_code
 
-- results
select accn_id, cncat=stuff(max(cncat),1,1,'')
from @t
group by accn_id;
 
-- clean up (optional)
update @t set cncat='';
go

Upvotes: 1

Related Questions