OraFAQ Forum: SQL & PL/SQL » Make columns out of records.

Home » SQL & PL/SQL » SQL & PL/SQL » Make columns out of records.

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Make columns out of records. [message #576812]

Fri, 08 February 2013 02:32

ErnstErnst
Messages: 17
Registered: January 2013

Junior Member

I know that the answer to any question could be found in the documentation.

Could anyone point me to the right place in the documentation, if I want to learn how to make columns out of records?

Report message to a moderator

Re: Make columns out of records. [message #576813 is a reply to message #576812]

Fri, 08 February 2013 02:36

Littlefoot
Messages: 16952
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What do you mean by that? What do you call a "record"? Is it one row in a table, or is it a (PL/SQL) group of related data items?

Report message to a moderator

Re: Make columns out of records. [message #576814 is a reply to message #576813]

Fri, 08 February 2013 02:42

ErnstErnst
Messages: 17
Registered: January 2013

Junior Member

My table has 2 columns: ID and column COL1.

Each ID has 5 records. I want each ID to have one record with the five COL1-values named C1, C2, C3, C4 and C5.

Report message to a moderator

Re: Make columns out of records. [message #576815 is a reply to message #576812]

Fri, 08 February 2013 02:46

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Fri, 01 February 2013 09:17

Michel Cadot wrote on Wed, 30 January 2013 15:43
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals...

...

Quote:

I know that the answer to any question could be found in the documentation.

You mean you don't want to waste your time searching in the documentation when we can waste ours doing it for you?

Regards
Michel

Report message to a moderator

Re: Make columns out of records. [message #576816 is a reply to message #576815]

Fri, 08 February 2013 02:47

Littlefoot
Messages: 16952
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you mean something like this?

SQL> select * from test order by id, col1;

        ID C
---------- -
         1 A
         1 B
         1 C
         1 D
         2 W
         2 X
         2 Y
         2 Z

8 rows selected.

SQL> select
  2    id,
  3    rtrim (xmlagg (xmlelement (e, col1 || ',')).extract ('//text()'), ',') new_col1
  4  from test
  5  group by id;

        ID NEW_COL1
---------- --------------------
         1 A,B,C,D
         2 X,Y,Z,W

SQL>

Report message to a moderator

Re: Make columns out of records. [message #576817 is a reply to message #576814]

Fri, 08 February 2013 02:47

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I want each ID to have one record with the five COL1-values named C1, C2, C3, C4 and C5.

And you are wrong, keep it as it is, it is far better, otherwise we will soon have the same question that is posted in several topics these last few days and you should read them.

Regards
Michel

Report message to a moderator

Re: Make columns out of records. [message #576818 is a reply to message #576815]

Fri, 08 February 2013 02:59

ErnstErnst
Messages: 17
Registered: January 2013

Junior Member

Michel Cadot wrote on Fri, 08 February 2013 02:46

From your previous topic:

Michel Cadot wrote on Fri, 01 February 2013 09:17
Michel Cadot wrote on Wed, 30 January 2013 15:43
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals...

...

Quote:
I know that the answer to any question could be found in the documentation.

You mean you don't want to waste your time searching in the documentation when we can waste ours doing it for you?

Regards
Michel

I mean that there could be certain commands that could be used for this purpose. Someone could know these commands and point me to where a can learn about it in the documentation.

Report message to a moderator

Re: Make columns out of records. [message #576820 is a reply to message #576816]

Fri, 08 February 2013 03:09

ErnstErnst
Messages: 17
Registered: January 2013

Junior Member

Littlefoot wrote on Fri, 08 February 2013 02:47

Did you mean something like this?

SQL> select * from test order by id, col1;

        ID C
---------- -
         1 A
         1 B
         1 C
         1 D
         2 W
         2 X
         2 Y
         2 Z

8 rows selected.

SQL> select
  2    id,
  3    rtrim (xmlagg (xmlelement (e, col1 || ',')).extract ('//text()'), ',') new_col1
  4  from test
  5  group by id;

        ID NEW_COL1
---------- --------------------
         1 A,B,C,D
         2 X,Y,Z,W

SQL>

I mean like this(if every ID had 4 records):

SQL>

ID  C1  C2  C3 C4
--  --  --  -- --
1   A   B   C  D
2   X   Y   Z  W

SQL>

Report message to a moderator

Re: Make columns out of records. [message #576821 is a reply to message #576820]

Fri, 08 February 2013 03:20

Littlefoot
Messages: 16952
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK, so just split it!

SQL> with part_1 as
  2    (select
  3       id,
  4       rtrim (xmlagg (xmlelement (e, col1 || ',')).extract ('//text()'), ',') new_col1
  5     from test
  6     group by id
  7    )
  8  select id,
  9    regexp_substr(new_col1, '\w+', 1, 1) c1,
 10    regexp_substr(new_col1, '\w+', 1, 2) c2,
 11    regexp_substr(new_col1, '\w+', 1, 3) c3,
 12    regexp_substr(new_col1, '\w+', 1, 4) c4
 13  from part_1;

        ID C1    C2    C3    C4
---------- ----- ----- ----- -----
         1 A     B     C     D
         2 X     Y     Z     W

SQL>

Report message to a moderator

Previous Topic:	Need to find fast
Next Topic:	Rebuild the table Structure (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 20:04:14 CDT 2013

Total time taken to generate the page: 0.10421 seconds