Converting row values in a table to a single concatenated string

Category : Articles User Rating : 4 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




I recently had to look at a problem that required converting the column values from a set of rows into a single comma separated string. On the face of it this is quite straightforward, but as with many things SQL Server there is more than one way to solve the problem. I’ve presented a few possible solutions here, no doubt there are more.

Moving on to problem, I'll create a table which can be used to demonstrate this (for the purposes of simplicity I’ve removed most of the columns).

Here is the table creation script :
CREATE TABLE ConcatenationDemo
(
 RowID   INT PRIMARY KEY  
,Txt     VARCHAR(MAX)
)
and here is the data (as a SQL script) :
INSERT INTO ConcatenationDemo
(RowID,Txt)
      SELECT 1,'A1'
UNION SELECT 2,'A2'
UNION SELECT 3,'A3'
UNION SELECT 4,'A4'
UNION SELECT 5,'A5'
UNION SELECT 6,'A6'
UNION SELECT 7,'A7'
UNION SELECT 8,'A8'
The requirement is to create a query that will concatenate the values in the ‘Txt’ column into a comma separated list. The query needs to return one row and one column so that the results pane in SQL Server Management Studio looks like this :

Concatenation Results

Solution 1 : Using a Recursive CTE

SQL Server 2005 opened up the possibility of using recursion within a SQL statement. Here is a recursive CTE that produces the required result :
;WITH CTE_Concatenated AS
    (
    SELECT  RowID,
            Txt
    FROM    ConcatenationDemo
    WHERE   RowID = 1
    UNION ALL
    SELECT  ConcatenationDemo. RowID
           ,CTE_Concatenated.Txt + ',' + ConcatenationDemo.Txt
    FROM    CTE_Concatenated
    JOIN    ConcatenationDemo
    ON      ConcatenationDemo. RowID =CTE_Concatenated.RowID + 1
    )
    SELECT  Txt
    FROM    CTE_Concatenated
    WHERE   RowID = (SELECT MAX(RowID) FROM ConcatenationDemo)
If I run this query then I get the result I want : The first part of the CTE is the anchor that sets the initial condition and the second part (after the UNION ALL) is the recursive part. I won’t explain this further here, however there is an excellent article about this subject on MSDN titled 'Recursive Queries Using Common Table Expressions'

Solution 2 : Using PIVOT

The first instinct when confronted with a problem which involves converting rows to columns may be to use PIVOT, and indeed the following code uses the PIVOT operator. It returns the same result as that above :
SELECT [1]+','+[2]+','+[3]+','+[4]+','+[5]+','+[6]+','+[7]+','+[8] AS Txt
FROM  (SELECT 'Total' AS AC,[1],[2],[3],[4],[5],[6],[7],[8]
       FROM   
          (SELECT RowID,Txt FROM ConcatenationDemo) AS B
       PIVOT (MAX(Txt) FOR RowID IN ([1],[2],[3],[4],[5],[6],[7],[8])
             ) AS A
      ) AS C
I’ve used the PIVOT command to convert the rows to an equivalent number of columns and then concatenated the result together. Normally we would have some type of aggregation as the rows are pivoted to columns, hence the use of the MAX() here. Note that in the this example no aggregation is necessary so this could equally well be a MIN(). One thing that you may have noticed is that the code is specific for the number of rows in the table – the code would have to be modified if there was an extra row. This is a significant limitation of this approach.

Solution 3 : Using a WHILE loop or Cursor

In the procedural world the chosen solution would probably be some sort of looping construct. We can do the same thing in SQL Server using either a while loop or cursor. The following code uses a WHILE loop (this could be rewritten to use a cursor) :
DECLARE @MaxCount INTEGER
DECLARE @Count INTEGER
DECLARE @Txt VARCHAR(MAX)
SET @Count = 1
SET @Txt = ''
SET @MaxCount = (SELECT MAX(RowID) FROM ConcatenationDemo)
WHILE @Count<=@MaxCount
    BEGIN
    IF @Txt!=''
        SET @Txt=@Txt+',' + (SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    ELSE
        SET @Txt=(SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    SET @Count=@Count+1
    END
SELECT @Txt AS Txt
Generally looping constructs in SQL are to be avoided as they can perform badly. However if the number of rows is small, as it is here, then this can be a useful approach. This solution is perhaps the easiest to understand and the most flexible, where performance isn’t an issue.

Solution 4 : Using SQL Concatenation

This solution is perhaps the most surprising, in that at first glance you might not expect it to work. The SQL below simply creates a variable then concatenates the value of the row. SQL iterates around each row in the table to produce the result. I adapted this solution from some code I found on a newsgroup a while ago, and was astonished that it worked. This behaviour is certainly unexpected, and also appears to be undocumented. As such it comes with a “health warning” as it maybe that Microsoft will remove the ability to do this in the future without warning. However for non-production code it’s a very simple solution.
DECLARE @Txt1 VARCHAR(MAX)
SET @Txt1=''
 
SELECT  @Txt1 = @Txt1 + Txt +','
FROM    ConcatenationDemo
SELECT  LEFT(@Txt1,LEN(@Txt1)-1) AS Txt

Solution 5 : Using FOR XML PATH

Some of the XML statements introduced in SQL Server 2005 had to implement a means of looping around data in order to produce XML. This solution takes advantage of this, but strips out the XML specific parts to produce the comma separated list.
SELECT STUFF((SELECT ',' + Txt
            FROM ConcatenationDemo
            FOR XML PATH('')) ,1,1,'') AS Txt

Solution 6 : Using the CLR

SQL Server 2005 introduced the ability to write logic using procedural code in C# or other dot net languages using the CLR. As the dot net framework provides richer capabilities for implementing logic such as string handling this is another possible way of implementing a solution.

Conclusion

I’ve given six possible solutions here. Each has its own merits and may be useful in differing circumstances. It’s likely that a real life requirement would be more complex than the simple example given here, so it wouldn’t be sensible to give a recommendation for which approach to adopt as this will vary according to the precise requirements. However the solution chosen is likely to be based on the performance required, code clarity and maintainability.
Link back to this article : http://www.sqlmatters.com/Articles/Converting row values in a table to a single concatenated string.aspx

Keywords

TSQL,Row concatenation,table,pivot


Comments
Post by Deep on Thu 18 Oct 2012 13:40. Report Inappropriate Post

Hi,

I have a requirement like....
Table : Employee
Row [ 0] : column [0] : has value = "Server"

Do something so in Table it will insert like
S
e
r
v
e
r
Post by Bob Murray on Wed 24 Oct 2012 15:15. Report Inappropriate Post

This post is fantastic - thank you!
Post by Nike on Thu 10 Jan 2013 16:51. Report Inappropriate Post

Hi, i want to get same multiple row from 1 row in table sql.
table "schedule"
Name Hospital Visit
------------------------------------
Ana RSIA 3
Budi RSIB 2

i want to create a query to get the result like :

Ana RSIA 3
Ana RSIA 3
Ana RSIA 3
Budi RSIB 2
Budi RBIB 2

Need your help to create a query in sql sever.
Thanks
Post by wajira on Fri 19 Apr 2013 10:05. Report Inappropriate Post

Thank you very much. Saved my day
Post by Bob on Wed 09 Oct 2013 21:26. Report Inappropriate Post

Exceptional article that continues to save some of us overworked developers TONS of time!

Thanks So Much!
Post by Daniel on Wed 23 Oct 2013 11:27. Report Inappropriate Post

Excelent article!!! Thanks a lot!!!
Post by Peter Sidi on Fri 10 Jan 2014 20:59. Report Inappropriate Post

Way to go!!!
Post by hari on Mon 17 Feb 2014 06:19. Report Inappropriate Post

Sir
.i have a table called employee in that empno,ename,job,sal,deptno are columns.
i want to retrieve the data in below format for each record in a table.
how to solve this?

empno:7784
ename='kevin'
job='account'
sal=15000
deptno=20
Post by Brahim on Tue 29 Apr 2014 15:52. Report Inappropriate Post

a much simpler solution without use of any loops and can be used for any number of rows :)

SELECT left(C, len(C) - 1) as ConcatText
FROM
(
SELECT Txt + ','

FROM ConcatenationDemo
FOR XML PATH('')
) AS D(C)
Post by bruce on Tue 17 Jun 2014 22:57. Report Inappropriate Post

Add one level of complexity, please, where a "group by" is implemented.

Specific example, say, people's color choices, in a table like this:

person, color
bruce, red
sam, blue
sam, orange
bruce, green
sally, purple
bruce, purple

after magic sql select, along the lines of this article, get this result:

bruce, "green, purple, red"
sally, "purple"
sam, "blue, orange"

so, we group by person, and the color field values are concatenated, with colors in ascending order. 10000 points to anyone who can come up with a simple, inline select that accomplishes this, along the lines of Brahmin's, or the author's excellent writeup.
Post by C on Tue 12 Aug 2014 18:37. Report Inappropriate Post

Select A.person
,(Select STUFF (
(Select ',' + CONVERT(VARCHAR(50),color)
from t1
where person = A.person
FOR XML PATH('')
)
,1,1,'') AS cols
) as color from t1 as A group by A.person
Post by candy on Tue 16 Sep 2014 10:07. Report Inappropriate Post

hi! how do I create a primary key that consists of both a string and a number and it also auto-increments?
Like
Emp.ID = A001....
Post by Sylvia on Thu 12 Mar 2015 06:10. Report Inappropriate Post

Thank you for this query - Select A.person
,(Select STUFF (
(Select ',' + CONVERT(VARCHAR(50),color)
from t1
where person = A.person
FOR XML PATH('')
)
,1,1,'') AS cols
) as color from t1 as A group by A.person


Made my day
Post by Prashant on Tue 14 Apr 2015 06:40. Report Inappropriate Post

is it possible with charindex() function sir
Post by Reece on Tue 30 Jun 2015 22:42. Report Inappropriate Post


In this case you are describing a programmatic solution, however, if you simply have a list of items that you want to turn into a comma separated list once-only, you can paste the list into this online tool:
http://convert.town/column-to-comma-separated-list
and a comma separated list will be produced.
Post by Justin on Thu 09 Jul 2015 04:52. Report Inappropriate Post

Solution 4 : Using SQL Concatenation is cool as shit ...

Thank you!
Post by Susi on Thu 09 Jul 2015 14:10. Report Inappropriate Post

I have records in a table by (inserted by other team)
Ex: tbl_claims (ID, code1,code2)
( 1,'A1','A2')
(2,'A2','A1')
At the end for Some report I need to sort these records like
( 1,'A1','A2')
(2,'A1','A2')
How to achieve this?
Post by Mark on Wed 04 May 2016 21:12. Report Inappropriate Post

Excellent! Thank you!
Post by anvesh on Mon 06 Jun 2016 20:52. Report Inappropriate Post

Nice Article !
This is my pleasure to read your article.
Really this will help to people of Database Community.

I have also prepared one article about, What should be our practice to store comma separated list in Database System.
You can also visit my article, your comments and reviews are most welcome.
http://www.dbrnd.com/2016/01/database-design-storing-a-comma-separated-list-in-a-database-is-a-bad-practice/
Post by Pam on Mon 27 Jun 2016 18:52. Report Inappropriate Post

I am not a developer and fairly new to SQL queries. I am writing a query to extract various fields from about 10 linked records and need to create a delimited CSV file, formatted one line per record appointment, even though I have multiple records due to all the files I am linking. I have patient demographic and appointment/service information that would only qualify once, but then I get into details as to the resource or provider attached to that appointment. Many of my appointments use a provider (person) and a location (room/equipment). Since the providers are stored in one table, and the location stored in another table, I get multiple lines for each appointment. I get this (abbreviated output) When I have one location and two providers, or two locations and one provider, I get six lines per appointment.

MRN Name Svc Rsrc type RSRC ID RSRC TIME Provider PROVIDER TIME
123456 Smith, Mary MRI L MRRMS 60 RADS 60
123456 Smith, Mary MRI P RAD 60 RADS 60
123456 Smith, Mary MRI P MRNRS 60 NRS 60

What I need to see is:

MRN NAME SVC RSRC RSRC RSRC RSRC PROV PROV PROV PROV PROV PROV TIME
CNT TYPE ID TIME CNT TIME CNT
123456 Smith Mary MRI 1 L MRRMS 60 1 RADS 60 2 NURSE 60

I will need to add some delimiters in the output but I think I can figure that out. I appreciate any assistance someone can give me! I can share my query if that is helpful.
Post by Bikesh srivastava on Fri 05 Aug 2016 11:04. Report Inappropriate Post
Website : http://bikeshsrivastava.blogspot.com/
// Create coloumn with seprated column value.from row value
SELECT DISTINCT p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role],
STUFF((SELECT distinct ',' + p1.Role
FROM finalsheet p1
WHERE p.[Contact ID]= p1.[Contact ID]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') RoleS
FROM finalsheet p
--group by p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role]
Post by Scott on Thu 11 Aug 2016 06:05. Report Inappropriate Post

What if I have a bunch of records (40k+) and each one has a foreign key stored in it for its upstream record. There could be N number of upstream records for each. How could I start from the child and traverse the foreign key to the parent, take a column from the parent and concatenate it to a column in the base child until I have reached the top of the tree for this child? Then go on to the next child till I have gone through all of the rows in the table.

something like:
child -> parent1 -> parent2 -> parent3 -> .. ->parent[x]

where we have the foreign key:
child.parentID = parent[x].recordID

and I want to store the concatenated hierarchy string:

child.tree = "parent[1].recordID/parent[2].recordID/parent[x+n].recordID"

This would become a trigger to execute when child is inserted or child.parentID is updated.

This won't run very often after the initial table population.
Post by mad on Tue 16 Aug 2016 03:39. Report Inappropriate Post

Table 1 Table2 Table 3
--------------

productNo Date Date Sale storeNo productNo
Pro 1 2016-06-11 2016-06-11 0 1 0
pro 2 2016-06-12 2016-06-12 1 2 pro 1
pro 3 2016-06-13 2016-06-13 3 3 pro2


Output should be
StoreNo Date ProductNO Sale
1 2016-06-11 Pro 1 0
pro 2 0
pro 3 0
2 2016-06-12 Pro 1 1
pro 2 0
pro 3 0
3 2016-06-13 Pro 1 0
pro 2 3
pro 3 0

I was trying for past three days and reading many article , i cant able to find any solution related to this ?

i tried but My output was like this !!

StoreNo Date productNo Sale
2 2016-06-12 Pro 1 1
3 2016-06-13 Pro 2 3

But my result should be diff , i dint know how to do it !!
if any one can help!!
Post by sushant on Wed 31 Aug 2016 12:29. Report Inappropriate Post

thanks
Post by Mark on Thu 01 Sep 2016 11:33. Report Inappropriate Post

thank you much!
The simple select into the variable is the easiest to understand and short, but as you mention, it could bite you down the road.
I ended up using the XML PATH approach.

I was headed down the PIVOT approach originally, but thought I'd give Google a shot first, and found your article. I do need to play more with Recursive CTE's, I like that method as well.

Post by Rodrigo Gomez Olivera on Fri 25 Nov 2016 21:30. Report Inappropriate Post

Thanks you! XML Path A great solution, without use Loop, while, variable etc.
Post by Lalitha on Thu 01 Dec 2016 10:06. Report Inappropriate Post

RejectionReason ID RejectionReasonID CompanyName

Confirm you will provide 84 84 XXXX
Supply copies of trade 85 85 XXXX

I want to Concatenate : Confirm you will provide ,Supply copies of trade for the particular companyname

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered