如何在Oracle SQL 11G中将列转置为行 [英] How to transpose column into row in oracle sql 11G

问题描述

我需要将列转换为以下行 select column_name from all_tab_cols where table_name='TABLE_NAME' ;

I need to convert column into row for the below select column_name from all_tab_cols where table_name='TABLE_NAME' ;

COLUMN_1
COLUMN_2
COLUMN_3
COLUMN_4
COLUMN_5
COLUMN_6
COLUMN_7

尝试使用数据透视运算符/子句

Tried using pivot operator/clause ,

如果表包含的列数更多，那么我需要提及该表的所有列名，而在in子句的数据透视函数中将无法提及

i need to mention all the column names for the table if the table contain more number of column it wouldnt be possible to mention in the pivot function in in clause,

select * from
(
    select column_name
    from all_tab_cols
    where table_name = 'TABLE_NAME'
)
pivot ( min(column_name) for column_name in 
(
'COLUMN_1', 'COLUMN_2', 'COLUMN_3', 'COLUMN_4', 'COLUMN_5', 'COLUMN_6', 'COLUMN_7'
));

预期输出:

COLUMN_1    COLUMN_2    COLUMN_3    COLUMN_4    COLUMN_5    COLUMN_6    COLUMN_7

任何人都可以请教如何将列转换为行

Could anyone Please advise how to convert column into rows

推荐答案

尝试一下:

select listagg(A,'  ') within group (order by A) as Names
from test

在这种情况下，查询如下:

In ur case the query goes like:

select listagg(column_name,'  ')  within group (order by column_name) as column_name
from all_tab_cols 
where table_name='TABLE_NAME' ;

这篇关于如何在Oracle SQL 11G中将列转置为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！