[MSSQL]如何仿照mysql的group_concat
發表於 : 2014年 7月 9日, 23:50
http://paladinprogram.blogspot.tw/2011/ ... oncat.html
http://stackoverflow.com/questions/4514 ... erver-2005
http://blog.xuite.net/tolarku/blog/39322307
測試程式碼:
結果:
aaaaa AAA,BBB,CCC
bbbbb DDD
或是:
結果:
aaaaa AAA,BBB,CCC,
bbbbb DDD,
http://stackoverflow.com/questions/4514 ... erver-2005
http://blog.xuite.net/tolarku/blog/39322307
測試程式碼:
代碼: 選擇全部
create table tbl_testa2
(data varchar(50), sid varchar(50))
insert into tbl_testa2 values('AAA','aaaaa')
insert into tbl_testa2 values('BBB','aaaaa')
insert into tbl_testa2 values('CCC','aaaaa')
insert into tbl_testa2 values('DDD','bbbbb')
SELECT sid, data = replace
((SELECT data AS [data()]
FROM tbl_testa2
WHERE sid = a.sid
ORDER BY sid FOR xml path('')), ' ', ',')
FROM tbl_testa2 a
WHERE sid IS NOT NULL
GROUP BY sid
aaaaa AAA,BBB,CCC
bbbbb DDD
或是:
代碼: 選擇全部
select sid, data=(select data +',' from tbl_testa2 where sid = a.sid for xml path('') )
from tbl_testa2 a
group by sid
aaaaa AAA,BBB,CCC,
bbbbb DDD,