Home » SQL & PL/SQL » SQL & PL/SQL » Need Help to convert rows into column (Oracle 11g)
Need Help to convert rows into column [message #652851] Tue, 21 June 2016 01:33 Go to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Hellow Friends,

I have following data in one column and wish to convert into columns.

A*B*C*D*E
F*G*H*I*J*K*L*M*N
O*P*Q*R*S
T*U*V*W*X*Y*Z

I need result into following format whereas number of columns may be more or less.
Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8 Col_9
    A     B     C     D     E
    F     G     H     I     J     K     L     M     N
    O     P     Q     R     S
    T     U     V     W     X     Y     Z

Your help would be appreciated

Thanks,
Chintan

Report message to a moderator

Re: Need Help to convert rows into column [message #652859 is a reply to message #652851] Tue, 21 June 2016 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.
drop table t;
create table t (val varchar2(20));
insert into t values ('A*B*C*D*E');
insert into t values ('F*G*H*I*J*K*L*M*N');
insert into t values ('O*P*Q*R*S');
insert into t values ('T*U*V*W*X*Y*Z');
commit;
SQL> col "Col_1" format a5
SQL> col "Col_2" format a5
SQL> col "Col_3" format a5
SQL> col "Col_4" format a5
SQL> col "Col_5" format a5
SQL> col "Col_6" format a5
SQL> col "Col_7" format a5
SQL> col "Col_8" format a5
SQL> col "Col_9" format a5
SQL> with
  2    data as (
  3      select val, row_number() over(order by null) rn
  4      from t
  5    ),
  6    split as (
  7      select rn, val, column_value elemnb,
  8             regexp_substr(val, '[^\*]+', 1, column_value) elem
  9      from data,
 10           table(cast(multiset(select level from dual
 11                               connect by level <= regexp_count(val,'\*')+1)
 12                      as sys.odciNumberList))
 13    )
 14  select val, "Col_1", "Col_2", "Col_3", "Col_4", "Col_5", "Col_6", "Col_7", "Col_8", "Col_9"
 15  from split
 16       pivot (max(elem)
 17              for elemnb in (1 "Col_1", 2 "Col_2", 3 "Col_3", 4 "Col_4",
 18                             5 "Col_5", 6 "Col_6", 7 "Col_7", 8 "Col_8",
 19                             9 "Col_9"))
 20  order by rn
 21  /
VAL                  Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8 Col_9
-------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
A*B*C*D*E            A     B     C     D     E
F*G*H*I*J*K*L*M*N    F     G     H     I     J     K     L     M     N
O*P*Q*R*S            O     P     Q     R     S
T*U*V*W*X*Y*Z        T     U     V     W     X     Y     Z

4 rows selected.

Report message to a moderator

Re: Need Help to convert rows into column [message #652890 is a reply to message #652859] Tue, 21 June 2016 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or shorter:
SQL> col "Col_1" format a5
SQL> col "Col_2" format a5
SQL> col "Col_3" format a5
SQL> col "Col_4" format a5
SQL> col "Col_5" format a5
SQL> col "Col_6" format a5
SQL> col "Col_7" format a5
SQL> col "Col_8" format a5
SQL> col "Col_9" format a5
SQL> with
  2    data as (
  3      select val, column_value elemnb,
  4             regexp_substr(val, '[^\*]+', 1, column_value) elem
  5      from t,
  6           table(cast(multiset(select level from dual
  7                               connect by level <= regexp_count(val,'\*')+1)
  8                      as sys.odciNumberList))
  9    )
 10  select val, "Col_1", "Col_2", "Col_3", "Col_4", "Col_5", "Col_6", "Col_7", "Col_8", "Col_9"
 11  from data
 12       pivot (max(elem)
 13              for elemnb in (1 "Col_1", 2 "Col_2", 3 "Col_3", 4 "Col_4",
 14                             5 "Col_5", 6 "Col_6", 7 "Col_7", 8 "Col_8",
 15                             9 "Col_9"))
 16  /
VAL                  Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8 Col_9
-------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
A*B*C*D*E            A     B     C     D     E
O*P*Q*R*S            O     P     Q     R     S
T*U*V*W*X*Y*Z        T     U     V     W     X     Y     Z
F*G*H*I*J*K*L*M*N    F     G     H     I     J     K     L     M     N

4 rows selected.

Report message to a moderator

Re: Need Help to convert rows into column [message #652896 is a reply to message #652890] Wed, 22 June 2016 00:14 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks a lot michel,

Is there any way to use sub query in pivot instead on writing columns?
 pivot (max(elem)
              for elemnb in (1 "Col_1", 2 "Col_2", 3 "Col_3", 4 "Col_4",
                            5 "Col_5", 6 "Col_6", 7 "Col_7", 8 "Col_8",
                            9 "Col_9"))

Regards,
Chintan

Report message to a moderator

Re: Need Help to convert rows into column [message #652899 is a reply to message #652896] Wed, 22 June 2016 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why?

Report message to a moderator

Re: Need Help to convert rows into column [message #652915 is a reply to message #652899] Wed, 22 June 2016 04:13 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Because number of columns may be very, therefor sub query will be better than fix values.

Thanks,
Chintan

Report message to a moderator

Re: Need Help to convert rows into column [message #652938 is a reply to message #652915] Wed, 22 June 2016 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no way to write it in SQL with a variable number of columns.
You have to do it in PL/SQL.

Note that you can write a query with the maximum of columns just adding them in the current query.

Report message to a moderator

Re: Need Help to convert rows into column [message #652962 is a reply to message #652938] Thu, 23 June 2016 03:02 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Miclel sorry to ask you again but i have ***** instead of * between the text, so please suggest what should i do?

Regards,
Chintan

Report message to a moderator

Re: Need Help to convert rows into column [message #652965 is a reply to message #652962] Thu, 23 June 2016 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean multiple '*' is the separator? Or a single '*' is the separator but there can be empty values?
In the former, fix or variable number of '*'?

Report message to a moderator

Re: Need Help to convert rows into column [message #652968 is a reply to message #652965] Thu, 23 June 2016 03:35 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
Yes, 5 Start (*****) is the separator

Report message to a moderator

Re: Need Help to convert rows into column [message #652970 is a reply to message #652968] Thu, 23 June 2016 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just replace "val" by "replace(val,'*****','*')" in the 2 regexp functions above.

Report message to a moderator

Re: Need Help to convert rows into column [message #652971 is a reply to message #652968] Thu, 23 June 2016 03:44 Go to previous messageGo to next message
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
Senior Member
I have changed separator from one star to five star and its working fine

VAL                                                Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8 Col_9
-------------------------------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
A*****B*****C*****D****E                           A     B     C     D     E
F*****G*****H*****I*****J*****K*****L*****M*****N  F     G     H     I     J     K     L     M     N
O*****P*****Q*****R*****S                          O     P     Q     R     S
T*****U*****V*****W*****X*****Y*****Z              T     U     V     W     X     Y     Z

Now, I have one another problem that, I have table which has around 100000 rows. While i applied above query logic on that it responds very slow.
Do you have any idea to fast it?

Thanks,
Chintan

Report message to a moderator

Re: Need Help to convert rows into column [message #652974 is a reply to message #652971] Thu, 23 June 2016 03:50 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As always, you have to post the execution plan to get a clear answer on a SQL question.

Report message to a moderator

Previous Topic: Better query
Next Topic: ORA-29278: SMTP transient error: 421 Service not available
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 17:59:57 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.