Concat column in table

Question

I have the following table test

id       Name      
1        AA        
1        TT       
1        V         
2        C         
3        B         
3        N         
4        N1

how can I get the following table as result : concat (Name,|) group by id .

  id       Name      
    1        AA|TT|V                     
    2        C         
    3        B|N               
    4        N1

I am pretty sure that you tagged it MySQL, or am I wrong? – Patrick Hofman May 09 '14 at 11:56 — Patrick Hofman, May 09 '14 at 11:56

score 0 · Accepted Answer · answered May 09 '14 at 11:59

There is no group_concat function available in SQL Server. You can achieve your result using below query. May not be an efficient one but give it a go. Demo fiddle here http://sqlfiddle.com/#!3/d3337/6

select distinct id,NEWNAME
from
(
SELECT 
    G.id,
    stuff(
    (
    select cast('|' as varchar(max)) + U.Name
    from tab U
    WHERE U.id = G.id
    order by U.Name
    for xml path('')
    ), 1, 1, '') AS NEWNAME
FROM
    tab G
 ) X
ORDER BY
    id ASC;

It will result in

enter image description here

Concat column in table

1 Answers1