表 table1 有以下数据：
STL_ID STL_NAME STL_INVOICE STL_COUNT
1 NAME1 INVOICE1 10
1 NAME1 INVOICE2 20
1 NAME1 INVOICE3 30
2 NAME2 INVOICE4 10
2 NAME2 INVOICE5 30
希望最后的统计结果如下：
STL_ID STL_NAME STL_INVOICE STL_COUNT
1 NAME1 INVOICE1,INVOICE2,INVOICE3 60
2 NAME2 INVOICE4,INVOICE5 40
请问SQL语句要怎么写？
sql语句
------解决方案--------------------
楼主用函数wmsys.wm_concat看看
------解决方案--------------------
select t2.id,
t2.name,
ltrim(max(SYS_CONNECT_BY_PATH(t2.invoice, ',')), ',') col,
max(t2.amt) amt
from (select t.*,
ROW_NUMBER() OVER(PARTITION BY t.id, t.name ORDER BY t.invoice) AS RN,
sum(t.stl_count) over(partition by t.id, t.name order by t.invoice) as amt
from test t) t2
start with rn = 1
connect by prior rn = rn - 1
and prior t2.id = t2.id
group by t2.id, t2.name
order by t2.id;
------解决方案--------------------
SELECT STL_ID,STL_NAME,
WM_CONCAT(STL_INVOICE) STL_INVOICE,
SUM(STL_COUNT) STL_COUNT
FROM TABLE1
GROUP BY STL_ID,STL_NAME
ORDER BY STL_ID,STL_NAME
------解决方案--------------------
还可以用listagg
------解决方案--------------------
10g的话用分组函数wm_concat()
select
stl_id,stl_name,wm_concat(stl_invoice) stl_invoice ,sum(stl_count) stl_count
group by stl_id,stl_name
order by stl_id,stl_name;
------解决方案--------------------
3楼的最简洁
10G上的版本wm_concat
11g listagg
------解决方案--------------------
wm_concat在12c已经废弃，建议用listagg
------解决方案--------------------
+1