CODEWITHSUNDEEP
oracle-database
Wella
Wella

Reputation: 1476

How to select row data as column in Oracle

I have two tables like bellow shows figures

enter image description here

enter image description here

I need to select records as bellow shown figure. with AH_ID need to join in second table and ATT_ID will be the column header and ATT_DTL_STR_VALUE need to get as that column relevant value

Required output

enter image description here

Upvotes: 1

Views: 1361

Answers (1)

ajw0100
ajw0100

Reputation: 378

Sounds like you have an Entity-Attribute-Value data model which relational DBs aren't the best at modeling. You may want to look into a key-value store.

However, as Justin suggested, if you're using 11g you can use th pivot clause as follows:

SELECT *
FROM (
      SELECT T1.AH_ID, T1.AH_DESCRIPTION, T2.ATT_ID, T2.ATT_DTL_STR_VALUE
      FROM T1 
        LEFT OUTER JOIN T2 ON T1.AH_ID = T2.AH_ID
     )
PIVOT (MAX(ATT_DTL_STR_VALUE) FOR (ATT_ID) IN (1));

This statement requires you to hard-code in ATT_ID however there are ways to do it dynamically. More info can be found here.

Upvotes: 1

Related Questions