Convert Multiple Rows into Single Column Comma Seperated - SQL Server 2008

Question

I would like to convert unknown number of rows into one columns as thus:

My table....

EMPLOYEENO NAMES        CODE        AMOUNT 
105        NAHYA ODA    BASESAL     234000
105        NAHYA ODA    249         35000
105        NAHYA ODA    250         37000
100        MAURICE RICH 249         23000
100        MAURICE RICH 250         21000
100        MAURICE RICH BASESAL     295000
107        TRINAH MYJOY 332         15000
107        TRINAH MYJOY 249         12400
107        TRINAH MYJOY LUNCHALLOW  66000
107        TRINAH MYJOY BASESAL     245000
107        TRINAH MYJOY 133         56100
107        TRINAH MYJOY 124         27400

convert into this....

EMPLOYEENO NAMES        OTHERDEDUCT 
105        NAHYA ODA    249-35000,250-37000
100        MAURICE RICH 249-23000,250-21000
107        TRINAH MYJOY 332-15000,249-12400,133-56100,124-27400

The new column "OTHERDEDUCT" should only include CODES which are numeric and exclude 'basesal & lunchallow'

Kindly see my code below, am stuck at the point of trying to merge code & amount into a string.

Your help is much appreciated.

DECLARE @r VARCHAR(MAX), @n INT, @i INT
SELECT @i = 1,
       @r = 'SELECT EmployeeNo, Names, ' + CHAR(13),
       @n = (SELECT TOP 1 COUNT( Code )
                   FROM [platinum].[dbo].[oda]
                  GROUP BY EmployeeNo, NAMES
                  ORDER BY COUNT( Code ) DESC ) ;     
WHILE @i <= @n BEGIN
           SET @r = @r +
           CASE WHEN @i = 1 
                THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '
                                 THEN Code 
                                            ELSE SPACE(0) END ) + ' + CHAR(13)
           WHEN @i = @n
             THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '
                                 THEN '', '' + Code
                                 ELSE SPACE(0) END ) ' + CHAR(13)
             ELSE 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + '
                                 THEN '', '' + Code
                                 ELSE SPACE(0) END ) + ' + CHAR(13) 
           END ;
           SET @i = @i + 1 ;
END
SET @r = @r + '
    FROM ( SELECT EmployeeNo, Names, Code,
                  ROW_NUMBER() OVER ( PARTITION BY EmployeeNo ORDER BY Code )
             FROM [platinum].[dbo].[oda] p ) D ( EmployeeNo, Names, Code, Seq )
           GROUP BY EmployeeNo, NAMES;'
EXEC( @r ) ;

knkarthick24 · Accepted Answer · 2015-12-07T09:42:32.130

STUFF with FOR XML PATH will help in your case:

SELECT DISTINCT b.EMPLOYEENO,
                b.NAMES,
                Stuff((SELECT ',' + Cast(code AS VARCHAR(10)) + '-'
                              + Cast(amount AS VARCHAR(10))
                       FROM   Employee_Test a
                       WHERE  a.EMPLOYEENO = b.EMPLOYEENO
                              AND a.NAMES = b.NAMES
                              AND ISNUMERIC(A.CODE) = 1
                       FOR XML PATH('')), 1, 1, '') AS OTHERDEDUCT
FROM   Employee_Test b
WHERE  ISNUMERIC(b.CODE) = 1 --this can be done in other ways also

OR

SELECT b.EMPLOYEENO,
       b.NAMES,
       Stuff((SELECT ',' + Cast(code AS VARCHAR(10)) + '-'
                     + Cast(amount AS VARCHAR(10))
              FROM   Employee_Test a
              WHERE  a.EMPLOYEENO = b.EMPLOYEENO
                     AND a.NAMES = b.NAMES
                     AND ISNUMERIC(A.CODE) = 1
              FOR XML PATH('')), 1, 1, '') AS OTHERDEDUCT
FROM   Employee_Test b
WHERE  ISNUMERIC(b.CODE) = 1 --handling of codes only with numbers can be checked with other way as well
GROUP  BY b.EMPLOYEENO,
          b.NAMES

thanks for you answer, you helped a great deal though the "Len(b.code) <4" doesn't seem to work — Peter Maurice Obar, Dec 07 '15 at 09:21
btw, i run the above query on a table with 800,000 and it took 8hrs 45mins to complete. is there a way it can be made faster? — Peter Maurice Obar, Dec 09 '15 at 15:14

Convert Multiple Rows into Single Column Comma Seperated - SQL Server 2008

1 Answers1