Concaternate rows into one column seperated by commas

Transact-SQL
1

I have a view that looks like this -

IH_ACCOUNT           DESCRIPTION         NUM
-----------------------------------------------------------------------------
1234                        ABC                        7
1234                        XYZ                         22
4567                        ERT                        12

The view holds only 10k of records.

I want to produce results like this:

1234                     ABC (7), XYZ (22)
4567                     ERT (12)

I have tried use CROSS APPLY but it is so slow. I have also tried

;with cte(IH_ACCOUNT,combined,rn)
as
(
  select IH_ACCOUNT, description + ' ('+''''+')', rn=ROW_NUMBER() over (PARTITION by IH_ACCOUNT order by description,num)
  from FREQ_PRODUCT
)
,cte2(IH_ACCOUNT,finalstatus,rn)
as
(
select IH_ACCOUNT, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.IH_ACCOUNT, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.IH_ACCOUNT = cte2.IH_ACCOUNT and cte.rn=cte2.rn+1
)
select IH_ACCOUNT, MAX(finalstatus) from cte2 group by IH_ACCOUNT

But both are very slow to run. Not sure if its my queries above, or if its the view of which its based on that is causing the issue.

The view itself is just doing a group on of multiple rows so I can have the Product descriptions grouped in this way (The underlying table data is a row for each product description multiple times (Its orders)) but if I execute that view it runs very quickly (less than 3 seconds).

2

Try this...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	IH_ACCOUNT INT NOT NULL,
	DESCRIPTION VARCHAR(20) NOT NULL,
	NUM INT NOT NULL,
	 PRIMARY KEY CLUSTERED (IH_ACCOUNT, DESCRIPTION)
	);
INSERT #TestData (IH_ACCOUNT, DESCRIPTION, NUM) VALUES
	(1234, 'ABC', 7),
	(1234, 'XYZ', 22),
	(4567, 'ERT', 12);

SELECT * FROM #TestData td;

--================================================

SELECT 
	STUFF((	
		SELECT 
			CONCAT(', ', td2.DESCRIPTION, ' (', td2.NUM, ')')
		FROM
			#TestData td2
		WHERE 
			td1.IH_ACCOUNT = td2.IH_ACCOUNT
		FOR XML PATH ('')
		), 1, 1, '')
FROM 
	#TestData td1
GROUP BY 
	td1.IH_ACCOUNT;
3

Thanks. I can get it it run, sadly it still takes around 15 mins to return the results. I think I might just have to put with the speed.

4

Try creating a temp table to hold the results from the view - and then perform the above against the temp table. If it still takes 15 minutes to run then there is something else going on...

If it runs in less time then the problem has to be how the view is constructed. If you don't want to use a temp table then you need to use the code in the view and figure out why that code is so slow when performing the above query.