Home » SQL & PL/SQL » SQL & PL/SQL » How to Convert Row to Column As Below (Oracle 11g)
How to Convert Row to Column As Below [message #671039] |
Mon, 13 August 2018 01:27 |
sonudev
Messages: 26 Registered: November 2009 Location: bangalore
|
Junior Member |
|
|
Hi,
I have a requirement as below and need to convert as shown below.
XYZ Raj A,B,C,D
and now I have to convert it as below where Raj will have one one values such as A, B, C and D assigned to one one times as below.
XYZ Raj A
Raj B
Raj C
Raj D
In how many ways it can be done?
Appreciate for the hints.
Thanks.
|
|
|
|
Re: How to Convert Row to Column As Below [message #671041 is a reply to message #671039] |
Mon, 13 August 2018 02:07 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Thu, 03 May 2012 15:39Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Michel Cadot wrote on Fri, 01 June 2012 11:51...
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.
...
Michel Cadot wrote on Fri, 01 June 2012 13:14Michel Cadot wrote on Fri, 01 June 2012 11:51...
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.
...
BlackSwan wrote on Thu, 20 February 2014 05:53It is your turn now to actually produce the SQL SELECT
Michel Cadot wrote on Thu, 20 February 2014 07:55
... and post the solutions of your previous topics.
You ask, you ask but you don't help people with the solution of your questions.
[Updated on: Mon, 13 August 2018 02:08] Report message to a moderator
|
|
|
Re: How to Convert Row to Column As Below [message #671042 is a reply to message #671041] |
Mon, 13 August 2018 02:40 |
sonudev
Messages: 26 Registered: November 2009 Location: bangalore
|
Junior Member |
|
|
Hi Watson/Michel,
Noted your points.
I have the table like this:
CREATE TABLE employees (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
sup_no Varchar2(10) DEFAULT 'XYZ' NOT NULL,
ename VARCHAR2(10)
);
and I got trying with listagg as below:
select listagg(ename,',') within group (order by ename) count
from employees;
count
------
A,B,C,D
Now, I am assuming data as A,B,C,D and trying to get
XYZ A
XYZ B
XYZ C
XYZ D
Hope it clarifies my question.
Thanks for your time.
|
|
|
|
|
Re: How to Convert Row to Column As Below [message #671045 is a reply to message #671044] |
Mon, 13 August 2018 03:02 |
sonudev
Messages: 26 Registered: November 2009 Location: bangalore
|
Junior Member |
|
|
Yes Watson noted my mistake.
Hi Michel,
When I tried like this, I got the result as below.
select ename|| ',' ename from employees order by ename;
count
-----
A,
B,
C,
D,
But I don't need commas and on removing comma, I am getting the output as below which I don't want. Any clues?
AA
BB
CC
DD
Thanks.
|
|
|
|
Re: How to Convert Row to Column As Below [message #671047 is a reply to message #671046] |
Mon, 13 August 2018 03:19 |
sonudev
Messages: 26 Registered: November 2009 Location: bangalore
|
Junior Member |
|
|
I did inserts as below:
INSERT INTO employees VALUES (1,'XYZ','A');
INSERT INTO employees VALUES (2,'XYZ','B');
INSERT INTO employees VALUES (3,'XYZ','C');
INSERT INTO employees VALUES (4,'XYZ','D');
Thanks.
|
|
|
|
Re: How to Convert Row to Column As Below [message #671049 is a reply to message #671048] |
Mon, 13 August 2018 04:02 |
sonudev
Messages: 26 Registered: November 2009 Location: bangalore
|
Junior Member |
|
|
Thanks Cookiemonster. I believe, I thought from different angle.
My aim is either to convert A,B,C,D
to
A
B
C
D
or vice versa.
Now we can use listagg function to output as A,B,C,D.
select listagg(ename,',') within group (order by ename) count
from employees;
Any other ways from 11g on wards?
Thanks everyone.
|
|
|
Re: How to Convert Row to Column As Below [message #671058 is a reply to message #671049] |
Mon, 13 August 2018 08:11 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sonudev wrote on Mon, 13 August 2018 05:02
My aim is either to convert A,B,C,D
to
A
B
C
D
or vice versa.
Now we can use listagg function to output as A,B,C,D.
select listagg(ename,',') within group (order by ename) count
from employees;
I must be dumb, but the query from your data is simply:
select ename from employees;
Or you are going to have to give a better explanation than you have so far from your data and what output you want, because your input and output keep changing.
There is no Raj in your insert statements.
You cannot possibly get a column called count from select ename|| ',' ename from employees order by ename;
Removing the comma cannot possibly turn A, into AA.
[Updated on: Mon, 13 August 2018 08:15] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:44:03 CDT 2024
|