0

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!

AYR
  • 1,139
  • 3
  • 14
  • 24
  • 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 Jun 12 '13 at 07: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 Jun 12 '13 at 09:02
  • I am working with 11g. The number of columns TEST_N is static and does not change. – AYR Jun 12 '13 at 09:27

1 Answers1

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
Sam
  • 1,358
  • 15
  • 24