如何在Oracle SQL中将表从列转置为行 [英] How to transpose a table from columns to rows in Oracle sql

问题描述

我在sql中有一个表，该表中有许多列，每列的数据类型相同.我想将表的列转置为行，以便所有数据都出现在1列中.我正在谈论的表类型的示例:

I have a table in sql which has a number of columns with the same type of data each column. I would like to transpose the table's columns into rows so that all of the data appears in 1 column. An example of the type of table I am talking about:

ID    DATE    TEST_1    TEST_2    TEST_3
----------------------------------------
1     1jan12    98        66       77
2     2jan12    75        89       72

进入:

ID    DATE        TEST       SCORE
-----------------------------------
1     1jan12      TEST_1      98
1     1jan12      TEST_2      66
1     1jan12      TEST_3      77
2     2jan12      TEST_1      75
2     2jan12      TEST_2      89
2     2jan12      TEST_3      72

提前感谢您的任何建议或指示！

Thanks in advance for any suggestions or directions!

推荐答案

一种选择是使用全部联盟":

One option would be to use 'Union All':

SELECT ID, DATE, 'TEST_1' AS TEST, TEST_1 AS SCORE
FROM TABLE
UNION ALL
SELECT ID, DATE, 'TEST_2' AS TEST, TEST_2 AS SCORE
FROM TABLE
UNION ALL
SELECT ID, DATE, 'TEST_3' AS TEST, TEST_3 AS SCORE
FROM TABLE

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