CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Basavaraj Navalagi
Basavaraj Navalagi

Reputation: 7

SQL Query String to be fetched as column

This is my table data table name-->mytable

id     string
1      A01,B01,C01
2      A02,B02,C02
3      A01,B01
4      C01,C02
5      C02,C03

i need output like below, anyone help here to write oracle SQL.

id |   A01 |  B01 | A02 | B02 | C01 | C02 | C03 |
1  |    Y  |  Y   |     |     |  Y  |     |     |
2  |       |      | Y   | Y   |     | Y   |     |
3  |    Y  |  Y   |     |     |     |     |     |        
4  |       |      |     |     |  Y  | Y   |     |
5  |       |      |     |     |     | Y   |  Y  |

Upvotes: 0

Views: 43

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try below (not exactly but somewhere) dynamic query to generate the same output -

select ID, TRANSLATE(A01, 10, 'Y ') A01, TRANSLATE(B01, 10, 'Y ') B01,
       TRANSLATE(A02, 10, 'Y ') A02, TRANSLATE(B02, 10, 'Y ') B02,
       TRANSLATE(C01, 10, 'Y ') C01, TRANSLATE(C02, 10, 'Y ') C02,
       TRANSLATE(C03, 10, 'Y ') C03
from(select id, value
       from (select distinct id, trim(regexp_substr(str,'[^,]+', 1, level)) value
               from t
            connect by regexp_substr(str, '[^,]+', 1, level) is not null
           order by id) t2)
pivot (COUNT(*) for value in ('A01' A01, 'B01' B01, 'A02' A02, 'B02' B02, 'C01' C01,
                              'C02' C02, 'C03' C03))
order by id;

You can pass your column list in the pivot clause and can select the same column as well.

Here is the DBfiddle.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use case expressions:

select id,
       (case when string like '%A01%' then 'Y' end) as a01,
       (case when string like '%B01%' then 'Y' end) as b01,
       . . .
from t;

Note: Because all your codes are three characters, you don't have to worry about conflicts (such as 'A01' matching 'BA01'. That somewhat simplifies the logic.

Upvotes: 1

Related Questions