StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Update</strong>: there is a much simpler solution than my first answer. It is more readable AND more elegant, I will therefore put it here first (As often, thanks to <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1769805100346245044" rel="nofollow noreferrer" title="Ask Tom">Tom Kyte</a>):</p> <pre><code>SQL> SELECT seq, 2 last_value(CASE 3 WHEN lvl = 1 THEN 4 descr 5 END IGNORE NULLS) over(ORDER BY seq) L1, 6 last_value(CASE 7 WHEN lvl = 2 THEN 8 descr 9 END IGNORE NULLS) over(ORDER BY seq) L2, 10 last_value(CASE 11 WHEN lvl = 3 THEN 12 descr 13 END IGNORE NULLS) over(ORDER BY seq) L3 14 FROM TEST; SEQ L1 L2 L3 ---------- ---------- ---------- ---------- 1 ONE 2 ONE TWO1 3 ONE TWO2 4 ONE TWO2 THREE1 5 ONE TWO3 THREE1 6 ONE TWO3 THREE2 </code></pre> <p>Following is my initial solution:</p> <pre><code>SQL> SELECT seq, 2 MAX(L1) over(PARTITION BY grp1) L1, 3 MAX(L2) over(PARTITION BY grp2) L2, 4 MAX(L3) over(PARTITION BY grp3) L3 5 FROM (SELECT seq, 6 L1, MAX(grp1) over(ORDER BY seq) grp1, 7 L2, MAX(grp2) over(ORDER BY seq) grp2, 8 L3, MAX(grp3) over(ORDER BY seq) grp3 9 FROM (SELECT seq, 10 CASE WHEN lvl = 1 THEN descr END L1, 11 CASE WHEN lvl = 1 AND descr IS NOT NULL THEN ROWNUM END grp1, 12 CASE WHEN lvl = 2 THEN descr END L2, 13 CASE WHEN lvl = 2 AND descr IS NOT NULL THEN ROWNUM END grp2, 14 CASE WHEN lvl = 3 THEN descr END L3, 15 CASE WHEN lvl = 3 AND descr IS NOT NULL THEN ROWNUM END grp3 16 FROM test)) 17 ORDER BY seq; SEQ L1 L2 L3 ---------- ---------- ---------- ---------- 1 ONE 2 ONE TWO1 3 ONE TWO2 4 ONE TWO2 THREE1 5 ONE TWO3 THREE1 6 ONE TWO3 THREE2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload