Question

我的问题是:

我不得不根据表格元数据(col格式)提出一些q问,结果如下:

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

是否可能使用Oraclek?

提前感谢!

Answer 1

如果你想要为每次电话提出询问,或使用硬编码的顶峰,那么你可以做这样的事情:

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

如果能够以这种形式做到这一点,

TABLENAME1|COL1,COL2
TABLENAME2|COL1,COL2,COL3

http://www.sqlsnippets.com/en/topic-11591.html。

Answer 2

您回顾的关键词是:pivot 。 rel=“nofollow noreferer”>。参见。 s子一般与MSSQL相同,因为我不了解Oracle的文献目录I ll tos up the 链接,希望比我更了解Ora网页布局的人会把它引到文件上。

EDIT:Oracle documentation,搜索足够的时间,请查阅细节。

友情链接