Union query through case

Question

My data looks like this

ID   Source Destination FLAG

1    A      B           Y

2    C      D           N

Resultant data is based on below logic

if FLAG='Y' then resultant query will lhave two rows with source and destination columns swaped else only one row

ID   Source Destination FLAG

1    A      B            Y

1    B      A            Y

2    C      D            N

Answer 1

It should be quite easy task to jumble the columns, and it should work. However, if you still insist to use case, then :

SQL> WITH data
  2       AS (SELECT 1   id,
  3                  'A' source,
  4                  'B' destination,
  5                  'Y' flag
  6           FROM   dual
  7           UNION ALL
  8           SELECT 2   id,
  9                  'C' source,
 10                  'D' destination,
 11                  'N' FLAG
 12           FROM   dual)
 13  SELECT *
 14  FROM  (SELECT id,
 15                source,
 16                destination,
 17                flag
 18         FROM   data
 19         UNION ALL
 20         SELECT id,
 21                CASE
 22                  WHEN flag = 'Y' THEN destination
 23                END AS "source",
 24                CASE
 25                  WHEN flag = 'Y' THEN source
 26                END AS "destination",
 27                flag
 28         FROM   data)
 29  WHERE  source IS NOT NULL
 30         AND destination IS NOT NULL
 31  ORDER  BY id
 32
SQL> /

Result:

        ID S D F
---------- - - -
         1 A B Y
         1 B A Y
         2 C D N

Answer 2

i think u don't need case in this fall, just change the places of source and destination where the Flag is 'Y' and add it to the original table filtered by id.

select id, source, destination, flag from table_name
union
select id, destination, source, flag from table_name
where flag = 'Y'
order by id