Home » SQL & PL/SQL » SQL & PL/SQL » Make columns out of records.
Make columns out of records. [message #576812] Fri, 08 February 2013 02:32 Go to next message
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?

Re: Make columns out of records. [message #576813 is a reply to message #576812] Fri, 08 February 2013 02:36 Go to previous messageGo to next message
Littlefoot
Messages: 19631
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?
Re: Make columns out of records. [message #576814 is a reply to message #576813] Fri, 08 February 2013 02:42 Go to previous messageGo to next message
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.
Re: Make columns out of records. [message #576815 is a reply to message #576812] Fri, 08 February 2013 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
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
Re: Make columns out of records. [message #576816 is a reply to message #576815] Fri, 08 February 2013 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 19631
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>
Re: Make columns out of records. [message #576817 is a reply to message #576814] Fri, 08 February 2013 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
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
Re: Make columns out of records. [message #576818 is a reply to message #576815] Fri, 08 February 2013 02:59 Go to previous messageGo to next message
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.

Re: Make columns out of records. [message #576820 is a reply to message #576816] Fri, 08 February 2013 03:09 Go to previous messageGo to next message
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>
Re: Make columns out of records. [message #576821 is a reply to message #576820] Fri, 08 February 2013 03:20 Go to previous message
Littlefoot
Messages: 19631
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>
Previous Topic: Need to find fast
Next Topic: Rebuild the table Structure (merged)
Goto Forum:
  


Current Time: Mon Sep 22 03:29:35 CDT 2014

Total time taken to generate the page: 0.07761 seconds