How to make row data comma separated in SQL?

Question

I am having one query which returns me following output.

(No of results not same all time, means sometimes it gives 3 category,sometimes 8 category etc..)

CategoryName

  Test1
  Test2
  Test3

Now i want that store procedure should return me these date in comma separated format. e.g. output string should be like: Test1,Test2,Test3

Can you please tell me how can i achieve this?

possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — OMG Ponies, Dec 07 '10 at 18:39
Why would you do this on the server side instead of within your client side app? — Juliet, Dec 07 '10 at 18:39

score 5 · Answer 1 · answered Dec 07 '10 at 18:45

this will work for all characters in your data:

set nocount on;
declare @YourTable table (BirthDay datetime, PersonName varchar(20))
insert into @YourTable VALUES ('1-10-2010',     'Joe'  )
insert into @YourTable VALUES ('2-10-2010',     'Bob  <&>'  )
insert into @YourTable VALUES ('2-10-2010',     'Alice')
set nocount off


--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
SELECT
    p1.BirthDay
        ,STUFF(
                   (SELECT
                        ', ' + p2.PersonName
                        FROM @YourTable p2
                        WHERE p2.BirthDay=p1.BirthDay
                        ORDER BY p2.PersonName
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS PersonNames
    FROM @YourTable p1
    GROUP BY p1.BirthDay

OUTPUT:

BirthDay                PersonNames
----------------------- ------------------------
2010-01-10 00:00:00.000 Joe
2010-02-10 00:00:00.000 Alice, Bob  <&>

(2 row(s) affected)

score 2 · Accepted Answer · answered Dec 07 '10 at 18:39

2

Try COALESCE or ISNULL:

DECLARE @returnValue varchar(MAX)

SELECT
  @returnValue = COALESCE(@returnValue + ', ', '') + CategoryName
FROM
  TableName

answered Dec 07 '10 at 18:39

Phil Hunt

8,404
1
30
25

OP doesn't say if CategoryName can be null or not. However, in the query, you might want to add a COALESCE or ISNULL around the CategoryName to protect from a NULL eliminating the entire string. It is a good habit to get into (protect from NULL when concatenating strings), which will prevent tricky mindless debugging later. – KM. Dec 07 '10 at 18:51

score 0 · Answer 3 · answered Dec 24 '13 at 05:30

Just modify the KM answer in a store procedure

ALTER Procedure [dbo].[Payroll_rptAbsentReport]
@FromDate DateTime,
@ToDate DateTime,
@GFacatoryID UniqueIdentifier
As
Begin
-- Temporary table for Row data seperation
CREATE TABLE TestTable(GEmployeeGenInfoID uniqueidentifier, dtAttendDateTime varchar(max))
INSERT INTO 
            TestTable(GEmployeeGenInfoID, dtAttendDateTime)
      SELECT 
             Payroll_tblAttendance.GEmployeeGenInfoID
            ,CONVERT(VARCHAR(max), dtAttendDateTime, 105)dtAttendDateTime
      FROM  Payroll_tblAttendance
            INNER JOIN PIS.dbo.PIS_tblEmployeeGenInfo as EmployeeGenInfo ON Payroll_tblAttendance.GEmployeeGenInfoID= EmployeeGenInfo.GEmployeeGenInfoID
            WHERE Payroll_tblAttendance.DayStatusID = 0  AND EmployeeGenInfo.GFactoryID=@GFacatoryID AND Payroll_tblAttendance.dtAttendDateTime Between @FromDate and @ToDate ORDER BY dtAttendDateTime
-- Final expected output
SELECT DISTINCT
      EmployeeGenInfo.StrEmpName
     ,EmployeeGenInfo.StrEmpID
     ,Attendence.CardNo
      ,EmployeeDesignation.StrDesignationName
      ,EmployeeDept.StrDepartmentName
      -- Count data will be in one column
      ,(Select COUNT(*) From TestTable Where GEmployeeGenInfoID=Attendence.GEmployeeGenInfoID) TotalAbsent
      -- Row data set into one column seperate by coma
      ,substring( ( SELECT ', ' + dtAttendDateTime as [text()]
                       FROM TestTable 
                       WHERE  GEmployeeGenInfoID = Attendence.GEmployeeGenInfoID
                       FOR XML path(''), elements
                     ), 3, 1000
                    )  List
FROM 
     Payroll_tblAttendance as Attendence
     INNER JOIN TestTable on TestTable.GEmployeeGenInfoID=Attendence.GEmployeeGenInfoID
     INNER JOIN PIS.dbo.PIS_tblEmployeeGenInfo as EmployeeGenInfo ON Attendence.GEmployeeGenInfoID= EmployeeGenInfo.GEmployeeGenInfoID
     INNER JOIN PIS.dbo.PIS_tblDesignationInfo as EmployeeDesignation ON EmployeeGenInfo.GDesignationInfoID= EmployeeDesignation.GDesignationInfoID
     INNER JOIN PIS.dbo.PIS_tblDepartment as EmployeeDept ON EmployeeGenInfo.GDepartmentID= EmployeeDept.GDepartmentID
     WHERE EmployeeGenInfo.GFactoryID=@GFacatoryID AND Attendence.DayStatusID = 0 AND Attendence.dtAttendDateTime Between @FromDate and @ToDate
DROP TABLE TestTable
END

score 0 · Answer 4 · answered Dec 07 '10 at 18:38

Have a look at something like (Full working example)

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(50)
)
INSERT INTO @Table (ID,Val) SELECT 1, 'A'
INSERT INTO @Table (ID,Val) SELECT 1, 'B'
INSERT INTO @Table (ID,Val) SELECT 1, 'C'
INSERT INTO @Table (ID,Val) SELECT 2, 'B'
INSERT INTO @Table (ID,Val) SELECT 2, 'C'

--Concat
SELECT  t.ID,
        STUFF((
                SELECT  ',' + t1.Val 
                FROM    @Table AS t1 
                WHERE   t1.ID = t.ID 
                FOR XML PATH('')
                ), 1, 1, '')
FROM    @Table t
GROUP BY t.ID

Also, you might find that Googling will provide a lot of answers.

this will not work if your data contains the common characters like: `<` or `>` or `&` etc. — KM., Dec 07 '10 at 18:43

score 0 · Answer 5 · answered Dec 07 '10 at 18:40

0

One means:

SELECT STUFF((
  SELECT ',' + CategoryName AS [text()]
  FROM YourTable
  FOR XML PATH('')
), 1, 1, '')

...but watch out for XML entities that will be escaped up - e.g. & => &

answered Dec 07 '10 at 18:40

Will A

24,780
5
50
61

1

you can easily avoid problems with the special XML characters, see my answer for how. – KM. Dec 07 '10 at 18:46

How to make row data comma separated in SQL?

5 Answers5