Tukukkk

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

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

;