CODEWITHSUNDEEP
sqloracle-database
Nishant
Nishant

Reputation: 63

merging data of different columns into specific columns in oracle

i have a result set like:

Name number reg_type1 reg_type2 reg_type3 reg_type4 reg_type5 reg_type6 reg_type7
aaa   123       Y         N       Y          N          Y       N         N
bbb   234       N         N       Y          N          Y       N         N
ccc   456       Y         N       Y          Y          N       Y         Y

and i want the result set like:

Name   number      level1       level2        level3      level4      level5 
aaa     123       reg_type1    reg_type3     reg_type5    NULL         NULL
bbb     234       reg_type3    reg_type5        NULL      NULL         NULL
ccc     456       reg_type1    reg_type3     reg_type4    reg_type6   reg_type7

can anyone please help me on this!!

Thanks!!

Upvotes: 2

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

If you are willing to have them in one column rather than separate columns, then the code is pretty easy:

select name, number,
       ((case when reg_type1 = 'Y' then 'reg_type1 ' else '' end) ||
        (case when reg_type2 = 'Y' then 'reg_type2 ' else '' end) ||
        (case when reg_type3 = 'Y' then 'reg_type3 ' else '' end) ||
        (case when reg_type4 = 'Y' then 'reg_type4 ' else '' end) ||
        (case when reg_type5 = 'Y' then 'reg_type5 ' else '' end) ||
        (case when reg_type6 = 'Y' then 'reg_type6 ' else '' end) ||
        (case when reg_type7 = 'Y' then 'reg_type7 ' else '' end)
       )
from t;

If you really want them in separate columns, you can do something like this:

select name, number,
       substr(regtypes, 1, 10) as level1,
       substr(regtypes, 11, 10) as level2,
       substr(regtypes, 21, 10) as level3,
       substr(regtypes, 31, 10) as level4,
       substr(regtypes, 41, 10) as level5,
       substr(regtypes, 51, 10) as level6,
       substr(regtypes, 61, 10) as level7
from (select name, number,
             ((case when reg_type1 = 'Y' then 'reg_type1 ' else '' end) ||
              (case when reg_type2 = 'Y' then 'reg_type2 ' else '' end) ||
              (case when reg_type3 = 'Y' then 'reg_type3 ' else '' end) ||
              (case when reg_type4 = 'Y' then 'reg_type4 ' else '' end) ||
              (case when reg_type5 = 'Y' then 'reg_type5 ' else '' end) ||
              (case when reg_type6 = 'Y' then 'reg_type6 ' else '' end) ||
              (case when reg_type7 = 'Y' then 'reg_type7 ' else '' end)
             ) as regtypes
      from t
     ) t

This version assumes that all the values have a length of 9 with a space between them.

Upvotes: 3

Related Questions