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 Go to next message
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 #671040 is a reply to message #671039] Mon, 13 August 2018 01:32 Go to previous messageGo to next message
John Watson
Messages: 7954
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. You know that you have to provide more. How about a CREATE TABLE statement? Or a SELECT?
Re: How to Convert Row to Column As Below [message #671041 is a reply to message #671039] Mon, 13 August 2018 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

BlackSwan wrote on Thu, 03 May 2012 15:39
Please 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:14
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.
...

BlackSwan wrote on Thu, 20 February 2014 05:53
It 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 Go to previous messageGo to next message
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 #671043 is a reply to message #671042] Mon, 13 August 2018 02:48 Go to previous messageGo to next message
John Watson
Messages: 7954
Registered: January 2010
Location: Global Village
Senior Member
You forgot the INSERT statements. You also omitted to use [code] tags. You know all this, please stop being lazy.
Re: How to Convert Row to Column As Below [message #671044 is a reply to message #671042] Mon, 13 August 2018 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> CREATE TABLE employees (
  2  empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  3  sup_no Varchar2(10) DEFAULT 'XYZ' NOT NULL,
  4  ename VARCHAR2(10)
  5  );

Table created.

SQL> select listagg(ename,',') within group (order by ename) count
  2  from employees;
COUNT
--------------------------------------------------------------------


1 row selected.
Re: How to Convert Row to Column As Below [message #671045 is a reply to message #671044] Mon, 13 August 2018 03:02 Go to previous messageGo to next message
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 #671046 is a reply to message #671045] Mon, 13 August 2018 03:05 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since you still haven't provided insert statements it's difficult to tell what data comes from what column in the table.
Post inserts.
Re: How to Convert Row to Column As Below [message #671047 is a reply to message #671046] Mon, 13 August 2018 03:19 Go to previous messageGo to next message
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 #671048 is a reply to message #671047] Mon, 13 August 2018 03:33 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you've over simplified your data.
Given what it is why don't you just do:

select sup_no, ename from employees order by ename;

Why is listagg involved?
Re: How to Convert Row to Column As Below [message #671049 is a reply to message #671048] Mon, 13 August 2018 04:02 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4947
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

Previous Topic: Dbms_metadat
Next Topic: Split Data in Column & Split Data in Excel (merged)
Goto Forum:
  


Current Time: Sun Jul 21 16:28:03 CDT 2019