Splitting a row at a given column

0
votes

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?

2
oraclesplitrows
You might want to add some sample input and output, specifically 4 rows or more of data. - Tim Biegeleisen
Broadly speaking this is a display issue and so should be solved in the UI rather than in a SQL query. Also do you care about which row of COL7, COL8, COL9, COL10, COL11, COL12 follows which row of COL1, COL2, COL3, COL4, COL5, COL6? - APC

2 Answers

1
votes

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;
1
votes

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!!