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 Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 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.
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 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.
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 16:26:11 CDT 2024
|