有很多方法可以合併多行資料的某一個column, 本篇是抄來的, 但非常受用, 下面的原文請參考:

There are many ways to use Oracle to pivot column data for display on a single row:
- Download SQL into Excel spreadsheet pivot table
Excel spreadsheets are a great way to pivot and analyze Oracle data, and tools like Excel-DB provide a fast API for downloading Oracle data into spreadsheets. Using excel pivot tables with Oracle data is a fast, easy way to use Oracle business intelligence without buying expensive OLAP solutions (Hyperion, Oracle BI Suite). http://www.excel-db.net/overview_business_intelligence_data_warehousing.htm#olap”>Here is an example.

1 - Write a PL/SQL function
You can write a PL/SQL function to display multiple rows values on a single line. http://www.oratechinfo.co.uk/delimited_lists_to_collections.html”>Martin Chadderton has written a Pl/SQL function called ”stragg” that you can define to display multiple SQL rows on one single line.

2 - Use the SYS_CONNECT_BY_PATH operator

http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=4385”>This article by Younes Naguib describes how to display multiple values from a single column in a single output row. In his example, he displays multiple values of the last name column on a single row. Note his use of the sys_connect_by_path and over operators:

select
deptno,
substr(SYS_CONNECT_BY_PATH(lname, ’,’),2) name_list
from
(
select
lname,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
from
igribun.emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;

DEPTNO NAME_LIST
1 Komers,Mokrel,Stenko
2 Hung,Tong
3 Hamer
4 Mansur

3 - Cross join

Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle cross join syntax. Matt notes that the Cross join ”has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)”.

SELECT
ite,
case
when ite = ’item1’ then item1
when ite = ’item2’ then item2
when ite = ’item3’ then item3
end as val
FROM
(
SELECT
pivoter.ite,
item1,
item2,
item3
FROM
someTable
CROSS JOIN
(
select ’item1’ as ite from dual
UNION ALL
select ’item2’ as ite from dual
UNION ALL
select ’item3’ as ite from dual
)pivoter
)

4 - Oracle analytic Lag-Over Function

Analytic functions have a pronounced performance improvement since they avoid an expensive self-join and only make one full-table scan to get the results. This site shows an example of using the Oracle LAG function to display multiple rows on a single column:



Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle cross join syntax. Matt notes that the Cross join ”has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)”.

Also see how to http://www.dba-oracle.com/t_sql_output_one_line.htm”>display Oracle SQL output rows on one single line.

SELECT
ite,
case
when ite = ’item1’ then item1
when ite = ’item2’ then item2
when ite = ’item3’ then item3
end as val
FROM
(
SELECT
pivoter.ite,
item1,
item2,
item3
FROM
someTable
CROSS JOIN
(
select ’item1’ as ite from dual
UNION ALL
select ’item2’ as ite from dual
UNION ALL
select ’item3’ as ite from dual
)pivoter
)

*本文引用自: http://www.dba-oracle.com/t_sql_pivoting_rows_several_columns.htm”>Oracle SQL - pivoting one row of several columns into one column of several rows, by Burleson Consulting