Converting multiple columns into rows [duplicate]

Question

I have an input table in Oracle like this. How do i get an output by converting the columns (year number) and corresponding values into rows

INPUT_TABLE

KEY_ID       YR1                  YR2                  YR3                  YR4    
A            1                    2                    3                    4      
B            8                    9                    10                   11     
C            15                   16                   17                   18     
D            70                   71                   75                   76

Expected OUTPUT

YEAR   A      B      C      D
1      1      8      15     70
2      2      9      16     71
3      3      10     17     75
4      4      11     18     76

Sample input and desired output

I have tried the Unpivot option but couldn't get it work for multiple columns (the years can go upto 20) and rows

Please don’t post images of code, error messages, or other textual data. We want sample input we can copy and paste. — tripleee
– tripleee, Commented Jul 1, 2024 at 4:52
Or stackoverflow.com/questions/29945504/… which is specifically about Oracle. Going forward, please search before asking. — tripleee
– tripleee, Commented Jul 1, 2024 at 4:56

farnaz · Accepted Answer · 2024-07-01 07:57:22Z

1

this query will work

SELECT *
FROM INPUT_TABLE 
 UNPIVOT(
  val
  FOR year
  IN(
    YR1 AS '1' , YR2 AS '2', YR3 AS '3', YR4 AS '4' ))
 PIVOT(
  SUM(val)
  FOR key_id
  IN(
   'A' AS A, 'B' AS B, 'C' AS C, 'D' AS D))

answered Jul 1, 2024 at 7:57

farnaz

784 bronze badges

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

Collectives™ on Stack Overflow

Converting multiple columns into rows [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related