Oracle SQL：将列转置为行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12482263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL: Transpose Columns to Rows
提问by Americo
There have been some questions on this but I couldn't find a relevant result for me. I have a query that gives me 2 columns of results:
对此有一些问题，但我找不到与我相关的结果。我有一个查询，它给了我 2 列结果：
Day Tot_dLS
01-Sep-12 10000
02-Sep-12 9920
I want to transpose the Date so it reads:
我想转置日期，使其显示为：
01-Sep-12 02-Sep-12
10000 9920
Is this possible?
这可能吗？
采纳答案by Justin Cave
Since you can return a fixed number of columns and you can use generic column names, you can do a standard pivot query
由于您可以返回固定数量的列并且可以使用通用列名，因此您可以执行标准的数据透视查询
SELECT max( case when rn = 1 then tot_dls else null end ) col_1,
max( case when rn = 2 then tot_dls else null end ) col_2,
max( case when rn = 3 then tot_dls else null end ) col_3,
<<25 more>>
max( case when rn = 29 then tot_dls else null end ) col_29,
max( case when rn = 30 then tot_dls else null end ) col_30
FROM (SELECT day,
tot_dls,
rank() over (order by day) rn
FROM your_table
WHERE day between date '2012-09-01'
and date '2012-09-02' -- Use whatever criteria you want here
)
回答by Andrea Bergia
Not really with a SQL query, since the same column would have to contain two different data types. You can get by with some tricks (casting everything to string) - but such things are much better done in the presentation application, or report, itself, than from the query.
不是真正的 SQL 查询，因为同一列必须包含两种不同的数据类型。您可以通过一些技巧（将所有内容转换为字符串）来解决问题 - 但是在演示应用程序或报告本身中完成这些事情比在查询中做得好得多。
回答by Tai Paul
This code:
这段代码：
create table your_table(day date, tot_dls number(5));
insert into your_table values ('01-SEP-2012',10000);
insert into your_table values ('02-SEP-2012',9920);
insert into your_table values ('03-SEP-2012',12020);
insert into your_table values ('04-SEP-2012',11030);
column dummy noprint
column "Header" format a7
column "Data" format a60
set hea off
SELECT 0 DUMMY
,'Day' "Header"
,LISTAGG(' ' || TO_CHAR(Day,'DD-MON-YYYY')) WITHIN GROUP (ORDER BY Day) "Data"
FROM your_table
UNION
SELECT 1
,'Tot_dls'
,LISTAGG(LPAD(TOT_DLS,13-LENGTH(TO_CHAR(TOT_DLS,'FM')),' ')) WITHIN GROUP (Order by Day)
FROM your_table
ORDER by 1;
produces this output using SQL*Plus on an Oracle 11g (11.2.0) database.
在 Oracle 11g (11.2.0) 数据库上使用 SQL*Plus 生成此输出。
Day 01-SEP-2012 02-SEP-2012 03-SEP-2012 04-SEP-2012
Tot_dls 10000 9920 12020 11030
回答by Taryn
You can use CASEstatements and an aggregate to perform this. You can use something like this:
您可以使用CASE语句和聚合来执行此操作。你可以使用这样的东西：
select max(case when day = '01-Sep-12' then Tot_dLS end) "01-Sep-12",
max(case when day = '02-Sep-12' then Tot_dLS end) "02-Sep-12",
........ add more columns here
from yourtable
then this would be expanded on to add more columns.
那么这将被扩展以添加更多列。
