CODEWITHSUNDEEP
sqloracle-database
Jindřich Mynarz
Jindřich Mynarz

Reputation: 1613

Split SQL results

I have an SQL query that projects several attributes:

SELECT a, b, c, d

I want to reshape the query results, so that each result is split into a UNION of the pairs of attributes, much like the following:

SELECT a AS x, b AS y
UNION
SELECT b AS x, c AS y
UNION
SELECT c AS x, d AS y

Is there a way to do so without reformulating or repeating the subquery that projects a, b, c, d?

In particular, I'm looking for a solution that works in the SQL dialect used by the Oracle Database (version 12).

Upvotes: 0

Views: 137

Answers (5)

Jindřich Mynarz
Jindřich Mynarz

Reputation: 1613

The WITH clause suggested by GauravS works as needed:

WITH inner AS (
  SELECT a, b, c, d
  ...
)
SELECT a, b
FROM inner
UNION
SELECT b, c
FROM inner
UNION
SELECT c, d
FROM inner

Upvotes: 0

MT0
MT0

Reputation: 167774

Use UNPIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( a,b,c,d ) AS
  SELECT 'a', 'b', 'c', 'd' FROM DUAL;

Query 1:

SELECT *
FROM   table_name
UNPIVOT( ( x, y ) FOR pair_index IN (
  ( a, b ) AS 1,
  ( b, c ) AS 2,
  ( c, d ) AS 3
) )

Results:

| PAIR_INDEX | X | Y |
|------------|---|---|
|          1 | a | b |
|          2 | b | c |
|          3 | c | d |

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

If you are ok with having all in one column separated by comma.

SELECT TRIM (both ',' FROM a ||',' ||b ||',' ||c ||',' ||d )  as sets
FROM   t 
GROUP  BY grouping sets( ( a, b ), ( b, c ), ( c, d ) ); 

| SETS |
|------|
|  4,5 |
|  1,2 |
|  2,3 |
|  5,6 |
|  3,4 |
|  6,7 |

demo

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

SQL> WITH test
  2       AS (SELECT 'a' a,
  3                  'b' b,
  4                  'c' c,
  5                  'd' d
  6             FROM DUAL),
  7       inter
  8       AS (SELECT ROWNUM rn, data, col
  9             FROM test UNPIVOT (col FOR data IN (a, b, c, d)))
 10  SELECT i1.col x, i2.col y
 11    FROM inter i1 JOIN inter i2 ON i2.rn = i1.rn + 1;

X Y
- -
a b
b c
c d

SQL>

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

SELECT
  CASE 
    WHEN rowgen.n=1 THEN main.a
    WHEN rowgen.n=2 THEN main.b
    WHEN rowgen.n=3 THEN main.c
  END as x,
  CASE 
    WHEN rowgen.n=1 THEN main.b
    WHEN rowgen.n=2 THEN main.c
    WHEN rowgen.n=3 THEN main.d
  END as y
FROM (SELECT a, b, c, d) main, 
     (SELECT 1 as n
      UNION ALL
      SELECT 2 as n
      UNION ALL
      SELECT 3 as n) rowgen

Upvotes: 2

Related Questions