Technical Article

Concatenate Rows without a cursor

,

You might come accross a situation where you want to retrieve the values of all the rows concatenated and stored in one variable. Here is the solution. Lets say you have a table called tblCustomerOrders which has CustID and OrderID. You now want all the OrderIDs for a particular Customer to be stored in one variable with comma delimited. e.g. For CustID = 1001, OrderID="1,2,3,4,5,6,7..and so on". Here is how you do it.

declare @lsOrderID varchar(8000)

update 
  tblCustomerOrders
set
  @lsOrderID = IsNull(@lsOrderID,'') + a.OrderID + ','
from
  tblCustomerOrders a
where
  a.CustID = '1001'

go

Rate

5 (1)

Share

Share

Rate

5 (1)