CODEWITHSUNDEEP
oracle-databasesplitrows
Landon Statis
Landon Statis

Reputation: 839

Splitting a row at a given column

I have a row with 12 columns. What I need to do for this application is create 2 rows, with the first 6 columns in first row, and the second 6 columns in the second row.

COL1   COL2   COL3   COL4   COL5   COL6   COL7   COL8   COL9   COL10   COL11   COL12

I need something like this:

    COL1   COL2   COL3   COL4   COL5   COL6
    COL7   COL8   COL9   COL10   COL11   COL12

Is this possible to achieve?

Upvotes: 0

Views: 73

Answers (2)

Popeye
Popeye

Reputation: 35900

Apart from tim's answer, you can also use connect by as following:

SELECT T.RN,
CASE WHEN LVL = 1 THEN COL1 ELSE COL7 END,
CASE WHEN LVL = 1 THEN COL2 ELSE COL8 END,
CASE WHEN LVL = 1 THEN COL3 ELSE COL9 END,
CASE WHEN LVL = 1 THEN COL4 ELSE COL10 END,
CASE WHEN LVL = 1 THEN COL5 ELSE COL11 END,
CASE WHEN LVL = 1 THEN COL6 ELSE COL12 END
FROM (SELECT ROWNUM RN, T.* FROM YOUR_TABLE T)
JOIN (SELECT LEVEL AS LVL
FROM DUAL CONNECT BY LEVEL <= 2) ON (1=1)

Cheers!!

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

If the columns be all of the same type, then a union query might work:

SELECT COL1, COL2, COL3, COL4, COL5, COL6 FROM yourTable
UNION ALL
SELECT COL7, COL8, COL9, COL10, COL11, COL12 FROM yourTable;

Upvotes: 1

Related Questions