SQL Query String to be fetched as column

Question

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  |

Gordon Linoff · Accepted Answer · 2021-06-29 12:05:35Z

1

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.

answered Jun 29, 2021 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Basavaraj Navalagi Over a year ago

thanks for your input here string contains some times 70 values comma separated then we need to write 70 case statement that is not valid right

Naveen Reddy CH Over a year ago

@BasavarajNavalagi But how are you going to decide how many columns should be there? What is the criteria for that?

Basavaraj Navalagi Over a year ago

max 80 columns will be there and string can change any time like instead of A01,B01 they put A1,B1 THEN We need to generate A1 and B1 column

Ankit Bajpai · Accepted Answer · 2021-06-29 12:28:03Z

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.

Collectives™ on Stack Overflow

SQL Query String to be fetched as column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related