用Oracle转置选择结果 [英] Transpose select results with Oracle

查看:53
本文介绍了用Oracle转置选择结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的问题是,有一些背景:

my question is, with some background:

我必须基于表元数据(列格式)生成一些sql查询,其结果类似于:

I have to generate some sql queries based on the table metadata (column format), and the result is something like:

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 ;-) */

是否可以使用Oracle SQL?

Is it possible using Oracle SQL?

提前谢谢!

推荐答案

如果要为每个调用生成查询或使用硬编码的max-column-count,则可以执行以下操作:

If you want to generate the query for each call or use a hardcoded max-column-count, then you can do something like that:

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

看看汤姆·凯特(Tom Kyte)的 stragg .

have a look at Tom Kyte's stragg.

这篇关于用Oracle转置选择结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆