菜鸟求一段SQL统计语句
表 table1 有以下数据：
STL_IDSTL_NAMESTL_INVOICESTL_COUNT
1NAME1 INVOICE110
1NAME1 INVOICE220
1NAME1 INVOICE330
2NAME2 INVOICE410
2NAME2 INVOICE530

希望最后的统计结果如下：

STL_IDSTL_NAMESTL_INVOICE STL_COUNT
1NAME1 INVOICE1,INVOICE2,INVOICE360
2NAME2 INVOICE4,INVOICE5 40

请问SQL语句要怎么写？


[解决办法]

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
[解决办法]

引用:

3楼的最简洁
10G上的版本wm_concat
11g listagg

+1