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 Go to next message
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 #286408 is a reply to message #286406] Fri, 07 December 2007 05:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Do you think you are the first person in the forum to ask this question

Search the forum for "Pivot"

Thumbs Up
Rajuvan.
Re: How to convert row col to row ? [message #286409 is a reply to message #286408] Fri, 07 December 2007 05:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Rajuvan is right. Put some effort into it. I'm sure you'll find examples here if you search for it.

MHE
Re: How to convert row col to row ? [message #286413 is a reply to message #286409] Fri, 07 December 2007 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 #286415 is a reply to message #286413] Fri, 07 December 2007 05:32 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member

Tons of thanks to all....
Re: How to convert row col to row ? [message #286416 is a reply to message #286413] Fri, 07 December 2007 05:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Point very well taken. Very Happy

But a search here with the terms "row" and "column" (the checkbox set to subject only) reveals
this thread... and this one ... and this one...

Like I said: a little bit of effort. Nothing more.

@tapaskmanna: do you know the maximum number of SNO columns you'll have? In SQL you need to know what columns you select.

MHE
Re: How to convert row col to row ? [message #286420 is a reply to message #286413] Fri, 07 December 2007 05:56 Go to previous messageGo to next message
Maaher
Messages: 7062
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 #286429 is a reply to message #286420] Fri, 07 December 2007 06:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks Maarten.
I noticed a while ago that the vast majority of search-results are other posts suggesting a search.
Now let's hope this one will make it to the top of the list Smile
Re: How to convert row col to row ? [message #286433 is a reply to message #286406] Fri, 07 December 2007 06:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Classic Demonstration Maarten !!!!!!

It Gives a Barbara touch to you code

( I am Barbara fan Smile )

Thumbs Up
Rajuvan.
Re: How to convert row col to row ? [message #286440 is a reply to message #286433] Fri, 07 December 2007 06:59 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: How to convert row col to row ? [message #286442 is a reply to message #286440] Fri, 07 December 2007 07:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maaher wrote on Fri, 07 December 2007 13:59

Edit: Cool! When you search for "pivot demonstration script" you'll find this thread.

lol.

Now if only people would use that search term Smile

[Updated on: Fri, 07 December 2007 07:10]

Report message to a moderator

Re: How to convert row col to row ? [message #286447 is a reply to message #286440] Fri, 07 December 2007 07:23 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Cool! When you search for "pivot demonstration script" you'll find this thread.

In this case it is worth to mention SnippetyJoe's site: http://www.sqlsnippets.com
and his SQL techniques pages: http://www.sqlsnippets.com/en/topic-12027.html:

Columns to Rows
Columns to String
Rows to Columns
Rows to String
String to Columns
String to Rows

Regards
Michel

Previous Topic: pl/sql anonymous block
Next Topic: Identifying floating point or fractional numbers in sql
Goto Forum:
  


Current Time: Sat Dec 03 03:59:59 CST 2016

Total time taken to generate the page: 0.09818 seconds