Oracle SQL dynamic pivot

Question

Imagine there are two tables table like so:

Table 1

ID   |   NAME   |   ATTR_A   |   ATTR_B   |   ATTR_C   |    ATTR_D
------------------------------------------------------------------
0        Bob               1            2            3          0  
1        Jane              2            0            4          3
2        Richard           1            3            5          4

Table 2

ID   |   TABLE_1_FK   |   ATTR_E
--------------------------------
0            0                 A
1            1                 B
2            1                 C
3            1                 D
4            2                 E
5            2                 F

Using a left join to merge table 1 and 2, the resulting table will be

Joined Table

NAME    |   ATTR_E    |   ATTR_A   |   ATTR_B   |   ATTR_C   |   ATTR_D
-----------------------------------------------------------------------
Bob                 A            1            2            3          0
Jane                B            2            0            4          3
Jane                C            2            0            4          3
Jane                D            2            0            4          3
Richard             E            1            3            5          4
Richard             F            1            3            5          4

Question

3 out of the 4 attributes (ATTR_A,B,C,D) in table 1 will always have a value greater than 0, create a new table so that if the attribute in table 1 has a value greater than 0, display it under a "table 1 attr" column, with its value under a "table 1 attr_value" column. Along with that, create a new column to describe the attribute selected.

The resulting table would be like so:

Result

NAME    |    ATTR_E    |    "table 1 attr"    |    "table 1 value"   |   "attr description"
---------------------------------------------------------------------------------------------
Bob                  A          ATTR_A                    1                   Apple
Bob                  A          ATTR_B                    2                   Banana
Bob                  A          ATTR_C                    3                   Carrot
Jane                 B          ATTR_A                    2                   Apple
Jane                 B          ATTR_C                    4                   Carrot
Jane                 B          ATTR_D                    3                   Durian
Jane                 C          ATTR_A                    2                   Apple
Jane                 C          ATTR_C                    4                   Carrot
Jane                 C          ATTR_D                    3                   Durian
Jane                 D          ATTR_A                    2                   Apple
Jane                 D          ATTR_C                    4                   Carrot
Jane                 D          ATTR_D                    3                   Durian
Richard              E          ATTR_A                    1                   Apple
Richard              E          ATTR_B                    3                   Banana
Richard              E          ATTR_C                    5                   Carrot
Richard              E          ATTR_D                    4                   Durian
Richard              F          ATTR_A                    1                   Apple
Richard              F          ATTR_B                    3                   Banana
Richard              F          ATTR_C                    5                   Carrot
Richard              F          ATTR_D                    4                   Durian

What have you tried so far?

Josh Eller
– Josh Eller

2018-11-19 16:15:45 +00:00
Commented Nov 19, 2018 at 16:15 — Josh Eller
– Josh Eller, Commented Nov 19, 2018 at 16:15
I'm really confused. Where does the description come from?

Gordon Linoff
– Gordon Linoff

2018-11-19 16:24:10 +00:00
Commented Nov 19, 2018 at 16:24 — Gordon Linoff
– Gordon Linoff, Commented Nov 19, 2018 at 16:24

Felypp Oliveira · Accepted Answer · 2018-11-19 16:24:09Z

1

You need to UNPIVOT your joined result. I'm posting a working example exactly like your problem, so you replace with your tables and such as stated in the sql comments.

SELECT *
FROM 
(
  -- your join result here...
  SELECT 1 col_id, 2 col_attrib_a, 0 col_attrib_b FROM dual UNION 
  SELECT 9 col_id, 0 col_attrib_a, 7 col_attrib_b FROM dual 
)
UNPIVOT
(
  value
  FOR value_type IN (col_attrib_a, col_attrib_b) -- the name of the attrib columns here...
)
WHERE 1=1
  AND value > 0
;

answered Nov 19, 2018 at 16:24

Felypp Oliveira

2,1971 gold badge17 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Oracle SQL dynamic pivot

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related