oracle转置查询的数据,sql – 使用Oracle转置选择结果

如果要为每个调用生成查询或使用硬编码的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,2,column_name ) c2,3,column_name ) c3,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

看看Tom Kyte的stragg.