SQL server combining rows to columns

Question

I had problem in combining two rows in to single column in SQL server. here is what I want to do my original table is like this

--------------------------------------------
| id   ||   D_code1  || D_code2  || Dcode3 |    
--------   ----------   --------   --------
| 1    ||   2f       || v012     || F013   |       
| 1    ||   k013     || c190     || NULL   |
--------------------------------------------

I want it to be loaded in like this. Its a bit complicated.

  ----------------------------------
  | id  ||  D_code                 |
   ----    ------------------------
  |  1  ||  2f,v012,F013,k013,c190 | 
  ----------------------------------

Could any one please help me on this. I am new to sql server. Thanks in advance.

Relational databases aren't designed to work well with data the way you want it. Are you sure you need it to be stored like that? Can't you put it in that format when you do the `SELECT` to retrieve it? — Dave.Gugg, Jul 01 '15 at 21:25

score 0 · Answer 1 · answered Jul 01 '15 at 21:50

you can use stuff and xml like that

     SELECT   distinct  i.id AS [id],
        STUFF((    SELECT ',' + t.d_code1+' ,' +t.d_code2+' , '+t.d_code3 AS [text()]

                    FROM mytable t
                   WHERE
                   t.id = i.id
                    FOR XML PATH('') 
                    ), 1, 1, '' )


        AS [dcode]
     FROM mytable i

score 0 · Answer 2 · answered Jul 01 '15 at 21:52

In the strict sense of how to achieve the result, one possible answer is a self join. Assuming that the key is a composite D_code1/D_code2/D_code3 then the query can be done in this way:

select t1.id,
isnull(t1.D_code1,'') + ',' +
isnull(t1.D_code2,'') + ',' +
isnull(t1.D_code3,'') + ',' +
isnull(t2.D_code1,'') + ',' +
isnull(t2.D_code2,'') + ',' +
isnull(t2.D_code3,'') as D_code
from t t1 
join t t2 on t1.id=t2.id 
and
isnull(t1.D_code1,'') + isnull(t1.D_code2,'') + isnull(t1.D_code3,'') 
<
isnull(t2.D_code1,'') + isnull(t2.D_code2,'') + isnull(t2.D_code3,'')

score 0 · Answer 3 · answered Jul 01 '15 at 22:25

You could use XML Path along with STUFF function to do this:

;WITH mytable1
AS (
    SELECT id
        ,CONCAT (D_code1,',',D_code2,',',ISNULL(Dcode3, '')) AS D_Code
    FROM mytable
    )
SELECT DISTINCT id
    ,REPLACE(LTRIM(RTRIM(REPLACE(STUFF((
            SELECT ',' + D_Code  
            FROM mytable1 T1
            WHERE T1.id = T2.id
            FOR XML PATH('')
            ), 1, 1, ''),',',' '))),' ',',') AS D_Code 
          --couldn't remember how to remove the succeeding commas if it is blank, 
          --so used a replace and trim to do this
FROM mytable1 T2

SQL Fiddle Demo

SQL server combining rows to columns

3 Answers3