sql rows to columns but in one line

Question

i have the following table

A                           B


mp1                     Y3013H301
mp2                     924071
mp2                     930081
mp3                     200130543
mp4                     3726474
mp5                     15/2014
mp5                     16/2014
mp6                     BBB032232
mp6                     BBB032572
mp6                     BBB032574
mp6                     BBB0325711

i would like to have the following output , indiferent on the number of A or B's :

A                   B

mp1                Y3013H301
mp2                924071 ; 930081
mp3                200130543
mp4                3726474
mp5                15/2014 ; 16/2014
mp6                BBB032232 ; BBB032572 ; BBB032574 ; BBB0325711

is there any way to do this? Thanks in advance!

This question has been answered before. Check out the answers to these questions on SO: [one](http://stackoverflow.com/q/20057655/249353), [two](http://stackoverflow.com/q/2046037/249353), [three](http://stackoverflow.com/q/273238/249353). These should get you started! — Josien, May 15 '14 at 11:43

score 2 · Accepted Answer · answered May 15 '14 at 11:41

You can do it using FOR XML PATH('') in the inner query to get all the values of B for each A.

select distinct T1.A,
    (select STUFF( (select ' ; ' + T2.B 
                from mytable T2
                where T1.A = T2.A
                FOR XML PATH('')
            ), 1, 3, '')) AS B
from mytable T1

SQL Fiddle Demo

sql rows to columns but in one line

1 Answers1