1、oracle中行转列函数有两种:
(1) to_char(wm_concat(…))
(2) listagg(…) within group (order by …)
WM_CONCAT函数在oracle12c的版本里无法识别,需要更换成 listagg(…) within group(order by…)函数,
(1)select user_name,to_char(wm_concat(course)) from WL_SOURCE_T group by user_name
(2)select s.user_name, listagg(s.course,',') within group (order by s.course) as courses from WL_SOURCE_T s group by s.user_name
2、mysql中行转列函数是:group_concat(…)
测试表中的数据。如下:
(1)按房屋编号获取对应的费用清单:
sql如下:
SELECT t.house, group_concat(t.type) FROM house t GROUP BY t.house
查询结果如下:
(2)如果对重复数据去重,sql如下:
SELECT t.house, group_concat(DISTINCT t.type) FROM house t GROUP BY t.house
查询结果如下:
(3)设置分隔符,sql如下:
SELECT t.house, group_concat(t.type SEPARATOR ";"), group_concat(t.money) FROM house t GROUP BY t.house
查询结果如下:
(4)排序,sql如下:
SELECT t.house, group_concat(t.money ORDER BY t.money DESC) FROM house t GROUP BY t.house