How to make row data comma separated in SQL?

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 c开发者_C百科omma separated format. e.g. output string should be like: Test1,Test2,Test3

Can you please tell me how can i achieve this?

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)

Try COALESCE or ISNULL:

DECLARE @returnValue varchar(MAX)

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

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.

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. & => &

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