Home » SQL & PL/SQL » SQL & PL/SQL » How to convert row col to row ?
How to convert row col to row ? [message #286406] |
Fri, 07 December 2007 05:01  |
tapaskmanna
Messages: 98 Registered: January 2007 Location: Cyprus,Nicosia
|
Member |
|
|
Column to Row
Select * from colrow
sno Name
1 Tapas
2 Tapas
3 Tapas
4 Tapas
1 Praveen
2 Praveen
Output for col to row :
Name Sno Sno Sno sno
Tapas 1 2 3 4
Praveen 1 2
How to convert row col to row ?
|
|
|
|
|
Re: How to convert row col to row ? [message #286413 is a reply to message #286409] |
Fri, 07 December 2007 05:26   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Although it is easy to search for "oracle column to row" in Google (and, actually, find the answer), I think that it is quite difficult for a newbie to search for an unknown term - "pivot" in this example. One might be able to explain what he/she needs and use several lines of text (or, even better, show us a simple example), but still not knowing that this whole story can be named by a single word: "pivot".
Further digging would probably reveal STRAGG, CONCAT_ALL etc., but - once again - for a newbie, these terms are unknown. So, push a newbie gently into the right direction and teach him how to fish.
|
|
|
|
|
Re: How to convert row col to row ? [message #286420 is a reply to message #286413] |
Fri, 07 December 2007 05:56   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Littlefoot wrote on Fri, 07 December 2007 12:26 | teach him how to fish.
| It has been a while since we actually showed how this can be done. So, pay attention class. Here is a demonstration script. The explanation is in the script itself.
REM PIVOT METHOD I: decode/case
REM ===========================
REM This script will take a three column table and pivot the valee rows to
REM columns for a category
REM the_id = unique identifier
REM the_cat = category
REM the_val = value
REM
REM Table creation:
CREATE TABLE tab_to_pivot( the_id NUMBER
, the_cat NUMBEr
, the_val VARCHAR2(10)
)
/
INSERT INTO tab_to_pivot VALUES ( 1, 1, 'One' );
INSERT INTO tab_to_pivot VALUES ( 2, 2, 'Two' );
INSERT INTO tab_to_pivot VALUES ( 3, 3, 'Three' );
INSERT INTO tab_to_pivot VALUES ( 4, 3, 'Four' );
INSERT INTO tab_to_pivot VALUES ( 5, 2, 'Five' );
INSERT INTO tab_to_pivot VALUES ( 6, 1, 'Six' );
INSERT INTO tab_to_pivot VALUES ( 7, 1, 'Seven' );
INSERT INTO tab_to_pivot VALUES ( 8, 1, 'Eight' );
INSERT INTO tab_to_pivot VALUES ( 9, 3, 'Nine' );
INSERT INTO tab_to_pivot VALUES (10, 2, 'Ten' );
INSERT INTO tab_to_pivot VALUES (11, 3, 'Eleven' );
INSERT INTO tab_to_pivot VALUES (12, 2, 'Twelve' );
INSERT INTO tab_to_pivot VALUES (13, 3, 'Thirteen');
COMMIT
/
SELECT *
FROM tab_to_pivot
/
REM step one: give a sequential number for each value per category:
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
/
COL the_val1 FORMAT A10
COL the_val2 FORMAT A10
COL the_val3 FORMAT A10
COL the_val4 FORMAT A10
COL the_val5 FORMAT A10
SET NULL "<null>"
REM step two: use this sequential number in a decode/case construction.
REM this allows us to break the values in 5 different columns.
REM we have at most 5 values in a category.
REM
SELECT the_cat
, DECODE( seqno, 1, the_val, NULL ) the_val1
, DECODE( seqno, 2, the_val, NULL ) the_val2
, DECODE( seqno, 3, the_val, NULL ) the_val3
, DECODE( seqno, 4, the_val, NULL ) the_val4
, DECODE( seqno, 5, the_val, NULL ) the_val5
FROM ( -- step 1
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
)
/
REM We almost have what we are looking for: we've distributed the values over 5 columns
REM Now we need to aggregate the values using a MAX()... GROUP BY:
SELECT the_cat
, MAX(the_val1) the_val1
, MAX(the_val2) the_val2
, MAX(the_val3) the_val3
, MAX(the_val4) the_val4
, MAX(the_val5) the_val5
FROM ( -- step 2
SELECT the_cat
, DECODE( seqno, 1, the_val, NULL ) the_val1
, DECODE( seqno, 2, the_val, NULL ) the_val2
, DECODE( seqno, 3, the_val, NULL ) the_val3
, DECODE( seqno, 4, the_val, NULL ) the_val4
, DECODE( seqno, 5, the_val, NULL ) the_val5
FROM ( -- step 1
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
)
)
GROUP BY the_cat
/
REM Cleanup
DROP TABLE tab_to_pivot PURGE
/
MHE
[edit]typo in script
[Updated on: Fri, 07 December 2007 06:54] Report message to a moderator
|
|
|
|
|
Re: How to convert row col to row ? [message #286440 is a reply to message #286433] |
Fri, 07 December 2007 06:59   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Thanks. To be complete, I was working on the "concatenation version" too. Here it is. Another demonstration script.
REM PIVOT METHOD II: Concatenation in a single column
REM =================================================
REM
REM This script will take a three column table and pivot the value rows to
REM a single column for a category
REM This can be useful if you don't know the number of columns or if you're only
REM interested in a single row list for each category.
REM
REM the_id = unique identifier
REM the_cat = category
REM the_val = value
REM
REM Table creation:
CREATE TABLE tab_to_pivot( the_id NUMBER
, the_cat NUMBEr
, the_val VARCHAR2(10)
)
/
INSERT INTO tab_to_pivot VALUES ( 1, 1, 'One' );
INSERT INTO tab_to_pivot VALUES ( 2, 2, 'Two' );
INSERT INTO tab_to_pivot VALUES ( 3, 3, 'Three' );
INSERT INTO tab_to_pivot VALUES ( 4, 3, 'Four' );
INSERT INTO tab_to_pivot VALUES ( 5, 2, 'Five' );
INSERT INTO tab_to_pivot VALUES ( 6, 1, 'Six' );
INSERT INTO tab_to_pivot VALUES ( 7, 1, 'Seven' );
INSERT INTO tab_to_pivot VALUES ( 8, 1, 'Eight' );
INSERT INTO tab_to_pivot VALUES ( 9, 3, 'Nine' );
INSERT INTO tab_to_pivot VALUES (10, 2, 'Ten' );
INSERT INTO tab_to_pivot VALUES (11, 3, 'Eleven' );
INSERT INTO tab_to_pivot VALUES (12, 2, 'Twelve' );
INSERT INTO tab_to_pivot VALUES (13, 3, 'Thirteen');
COMMIT
/
SELECT *
FROM tab_to_pivot
/
REM step one: give a sequential number for each value per category
REM we'll need that in step two.
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
/
COL the_val FORMAT A50
COL the_val1 FORMAT A10
COL the_val2 FORMAT A10
COL the_val3 FORMAT A10
COL the_val4 FORMAT A10
COL the_val5 FORMAT A10
SET NULL "<null>"
REM step two: use this sequential number in a hierarchical query
REM we can use Oracle's built-in SYS_CONNECT_BY_PATH
REM to concatenate the values.
SELECT the_cat
, SYS_CONNECT_BY_PATH(the_val,'/') the_val
FROM ( -- step 1
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
)
CONNECT BY the_cat = PRIOR the_cat
AND seqno = PRIOR seqno + 1
START WITH seqno = 1
/
REM We almost have what we are looking for: we've concatenated the values
REM But now we need to aggregate the values (we want only one for each category
REM We can do that using a MAX()... GROUP BY:
REM
REM We add a SUBSTR to get rid of the leading slash.
PROMPT Tadah!
PROMPT ======
SELECT the_cat
, MAX(SUBSTR(the_val,2)) the_val
FROM ( -- step 2
SELECT the_cat
, SYS_CONNECT_BY_PATH(the_val,'/') the_val
FROM ( -- step 1
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id) seqno
, the_cat
, the_val
FROM tab_to_pivot
ORDER BY the_cat, the_id
)
CONNECT BY the_cat = PRIOR the_cat
AND seqno = PRIOR seqno + 1
START WITH seqno = 1
)
GROUP BY the_cat
/
REM Cleanup
DROP TABLE tab_to_pivot PURGE
/
A CONCAT_ALL (a function built by Mr. Padfield) example can be found here.
MHE
Edit: Cool! When you search for "pivot demonstration script" you'll find this thread.
[Updated on: Fri, 07 December 2007 07:01] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 14:04:54 CST 2025
|