Concatenate Multi Row Column Value into One Row
Concatenate Multi Row Column Value into One Row
By : Kasim Wirama, MCDBA, MCITP
Sometimes you need concatenate multiple rows into 1 string value separated by delimiter (usually comma). For example to get orderdescription from same customerid like the example below :
Declare @order table (orderid int, custid int, orderdescription varchar(50));
insert into @order values (1,676,’order by CustA’);
insert into @order values (2,676,’order by CustB’);
Expected outcome is
Order by CustA, order by CustB
SQL Server 2005 onwards comes up with XML features, one of them is FOR XML PATH, here is the query to get desired output :
Declare @list varchar(max);
set @list = stuff (
(select ‘, ‘ + orderdescription as [text()]
from @order
when custid = 676
FOR XML PATH(‘’)
)
, 1, 2, ‘’);
select @list;
It looks okay, until there is evil input into @order table like below :
insert into @order values (2,676,’order by CustA & order by CustB’);
rerun the FOR XML PATH query again, the expected outcome is
order by CustA, order by CustB, order by CustA & order by CustB
If you notice ‘&’ is changed by XML PATH clause into ‘&’
The same thing would happen if the column value contains any HTML preserved character such as <, and >.
Better solution for reproducing single rows of concatenated values of rows is by using conventional one as following query below shown :
Declare @list varchar(max);
set @list = ‘’;
select @list = case @list when ‘’ then orderdescription
else @list + ‘, ‘ + orderdescription
end
from @order
when custid = 676;
select @list;