SQL 列轉行的實作

--列轉行，逗号拼接指定列的值

SQL Server中寫法：

SELECT STUFF(( SELECT  ',' + Field1 from TableA FOR XML PATH('')), 1, 1, '')

Oracle中寫法：

方法一：wmsys.wm_concat

select wmsys.wm_concat(Field1) from TableA

方法二：LISTAGG()

2.1、LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

用法就像聚合函數一樣，通過Group by語句，把每個Group的一個字段，拼接起來

1 with temp as(
 2   select 'China' nation,'Beijing' city from dual union
 3   select 'China' nation,'Shanghai' city from dual union
 4   select 'China' nation,'Guangzhou' city from dual union
 5   select 'USA' nation,'New York' city from dual union
 6   select 'USA' nation,'Bostom' city from dual
 7 )
 8 select nation,listagg(city,',') within group(order by city)
 9 from temp
10 group by nation;      

2.2、over(partition by XXX)

在不使用Group by語句時候，使用LISTAGG函數（當作SUM()函數來使用）

1 with temp as(
2   select 'China' nation,'Beijing' city from dual union
3   select 'China' nation,'Shanghai' city from dual union
4   select 'China' nation,'Guangzhou' city from dual union
5   select 'USA' nation,'New York' city from dual union
6   select 'USA' nation,'Bostom' city from dual
7 )
8 select nation,city,listagg(city,',') within group(order by city) over(partition by nation) rank
9 from temp;      

如果您看了本篇部落格,覺得對您有所收獲，請點選右下角的

[推薦]

如果您想轉載本部落格，

請注明出處

如果您對本文有意見或者建議，歡迎留言

感謝您的閱讀，請關注我的後續部落格