使用 Oracle 转置选择结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1788011/
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
Transpose select results with Oracle
提问by Zsolt Botykai
my question is, with some background:
我的问题是，有一些背景：
I have to generate some sql queries based on the table metadata (column format), and the result is something like:
我必须根据表元数据（列格式）生成一些sql查询，结果类似于：
TABLENAME1|COL1
TABLENAME1|COL2
TABLENAME2|COL1
TABLENAME2|COL2
TABLENAME2|COL3
TABLENAME3|COL1
TABLENAME4|COL1
TABLENAME4|COL2
... /*some other 1800 rows */
(Yeah, it's ordered.) What I need is to transpose this data, based on the first column, so the expected output would be:
（是的，它是有序的。）我需要的是根据第一列转置这些数据，所以预期的输出是：
TABLENAME1|COL1|COL2|NULL
TABLENAME2|COL1|COL2|COL3
TABLENAME3|COL1|NULL|NULL
TABLENAME4|COL1|COL2|NULL
/* less then 1800 rows ;-) */
Is it possible using Oracle SQL?
是否可以使用 Oracle SQL？
Thanks in advance!
提前致谢！
采纳答案by Peter Lang
If you want to generate the query for each call or use a hardcoded max-column-count, then you can do something like that:
如果您想为每次调用生成查询或使用硬编码的 max-column-count，那么您可以执行以下操作：
WITH tab AS
(
SELECT table_name, column_name FROM user_tab_cols WHERE column_id <= 4
) -- user_tab_cols used to provide test data, use your table instead
SELECT MAX(c1) c1,
MAX(c2) c2,
MAX(c3) c3,
MAX(c4) c4
FROM (SELECT table_name,
DECODE( column_id, 1, column_name ) c1,
DECODE( column_id, 2, column_name ) c2,
DECODE( column_id, 3, column_name ) c3,
DECODE( column_id, 4, column_name ) c4
FROM ( SELECT table_name,
column_name,
ROW_NUMBER() OVER ( PARTITION BY table_name ORDER BY column_name ) column_id
FROM tab
)
)
GROUP BY table_name
ORDER BY table_name
If it is sufficient to get it in that form
如果以那种形式获得它就足够了
TABLENAME1|COL1,COL2
TABLENAME2|COL1,COL2,COL3
have a look at Tom Kyte's stragg.
看看 Tom Kyte 的stragg。
回答by Donnie
The keyword that you're looking for is pivot. Here'san example of its use. The syntax appears generally the same as MSSQL, and since I don't know the url for Oracle's documentation I'll toss up the MSDNlink and hope that someone that knows more about Oracle's page layout than me will edit it to point to their documentation.
您要查找的关键字是pivot。 这是它的使用示例。语法看起来与 MSSQL 大致相同，因为我不知道 Oracle 文档的 url，我将扔掉MSDN链接，并希望比我更了解 Oracle 页面布局的人将编辑它以指向他们的文档.
EDIT: Oracle documentation, search for pivot enough times and you'll get to the details.
编辑：Oracle 文档，搜索数据透视足够次数，您将获得详细信息。
