How to transpose a table from columns to rows in Oracle sql [duplicate]

Question

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

Into:

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!

If you search StackOverflow for "Oracle pivot," there are a lot of questions that talk about how to do this depending on the requirements. What version of Oracle are you using? 11g has the PIVOT operator that earlier versions do not. Do you know the set of valid TEST values at compile time? Or do you want the number of columns in the result to change when a new TEST_4 row is inserted in the base table? — Justin Cave
– Justin Cave, Commented Jun 12, 2013 at 7:47
Not a duplicate I think (at least as far as case/decode are concerned), because he wants to go the other way around (columns => rows instead of rows => columns). — Sam
– Sam, Commented Jun 12, 2013 at 9:02
I am working with 11g. The number of columns TEST_N is static and does not change. — AYR
– AYR, Commented Jun 12, 2013 at 9:27

Sam · Accepted Answer · 2013-06-12 07:43:51Z

5

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

answered Jun 12, 2013 at 7:43

Sam

1,37616 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to transpose a table from columns to rows in Oracle sql [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related