[MSSQL] 合併多筆資料至同一欄

<原始資料> 

<SQL執行結果>

select ogb01, ogb04, Left(ogb03List, LEN(ogb03List)-1) as ogb03List
from (
select distinct ogb01, ogb04,
     (
    select cast(ogb03 as nvarchar) + ',' 
    from ogb_file as ogb2
    where ogb2.ogb01='ABC-0001'
      and ogb2.ogb04=ogb1.ogb04
    FOR XML PATH('')
     ) as ogb03List
from ogb_file ogb1
where ogb1.ogb01='ABC-0001'
) A

select distinct ogb01, ogb04,
     substring((
    select ',' + cast(ogb03 as nvarchar) 
    from ogb_file as ogb2
    where ogb2.ogb01='ABC-0001'
      and ogb2.ogb04=ogb1.ogb04
    FOR XML PATH('')
     ),2,1000) as ogb03List
from ogb_file ogb1
where ogb1.ogb01='ABC-0001'