Oracle SQL query output in a required format by adding columns

Question

I have oracle database table 'mytable' with following data-

ID          ORDER    ACTION     CB        TV
35547225    CHANGE<  Add        TV       100788581
35355239    CHANGE<  Add        G76763164 014580531
35779535    CHANGE   Add        TVservi   038894810
35591230    CHANGE<  Add        G87178597   
35814001    CHANGE   Rem        TVservi   011733179
35418962    CHANGE<  Rem        TV        011733179

I want to get output in following format with additional column as 'CT'-

ID          ORDER   ACTION  CB          TV          CT
35547225    CHANGE  Add                 100788581   Only TV
35355239    CHANGE  Add     G76763164   014580531   Both
35779535    CHANGE  Add                 038894810   Only TV
35591230    CHANGE  Add     G87178597               Only CB
35814001    CHANGE  Remove              011733179   Only TV
35418962    CHANGE  Remove              011733179   Only TV

I want following changes in output -

In 'ORDER' column - if it is 'CHANGE<' in 'mytable' then it should be 'CHANGE' in output.
in 'ACTION' column - if it is 'Rem' in 'mytable' then it should be 'Remove' in output.
In 'CB' column - if it is starting with G and then 8 digit number then keep as it is otherwise make it NULL in output
Add one more column 'CT' in output by writing remark based on following condition-
- If CB is NULL and TV is NOT NULL then 'Only TV'
- If CB is NOT NULL and TV is NOT NULL then 'Both'
- If CB is NOT NULL and TV is NULL then 'Only CB'

DECODE is your friend for all these things (or CASE).

user672739
– user672739

2018-10-04 15:14:51 +00:00
Commented Oct 4, 2018 at 15:14 — user672739
– user672739, Commented Oct 4, 2018 at 15:14

The Impaler · Accepted Answer · 2018-10-04 15:48:58Z

3

You can use the CASE clause to compute the various column values according to arbitrary logic:

select
    id,
    case when "order" = 'CHANGE' then 'CHANGE'
         when "order" = 'CHANGE<' then 'CHANGE'
    end as "order",
    case when action = 'Rem' then 'Remove'
         else action
    end as action,
    case when regexp_like(cb, 'G[0-9]{8}') then cb
         else null
    end as cb,
    tv,
    case when cb is null and tc is not null then 'Only TV'
         when cb is not null and tv is not null then 'Both'
         when cb is not null and tv is null then 'Only CB'
    end as ct
  from my_table

Note that order is a reserved word that you don't normally use as a column name. If you want to use it, you need to enclose it in double quotes (").

edited Oct 4, 2018 at 15:48

answered Oct 4, 2018 at 15:46

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

Oracle SQL query output in a required format by adding columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related