OraFAQ Forum: SQL & PL/SQL » How to Convert Row to Column As Below

Home » SQL & PL/SQL » SQL & PL/SQL » How to Convert Row to Column As Below (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: How to Convert Row to Column As Below [message #671040 is a reply to message #671039]

Mon, 13 August 2018 01:32

John Watson
Messages: 8929
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?

Report message to a moderator

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: 68641
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

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.

Report message to a moderator

Re: How to Convert Row to Column As Below [message #671043 is a reply to message #671042]

Mon, 13 August 2018 02:48

John Watson
Messages: 8929
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.

Report message to a moderator

Re: How to Convert Row to Column As Below [message #671044 is a reply to message #671042]

Mon, 13 August 2018 02:50

Michel Cadot
Messages: 68641
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.

Report message to a moderator

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.

Report message to a moderator

Re: How to Convert Row to Column As Below [message #671046 is a reply to message #671045]

Mon, 13 August 2018 03:05

cookiemonster
Messages: 13920
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.

Report message to a moderator

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.

Report message to a moderator

Re: How to Convert Row to Column As Below [message #671048 is a reply to message #671047]

Mon, 13 August 2018 03:33

cookiemonster
Messages: 13920
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?

Report message to a moderator

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.

Report message to a moderator

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

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 19:33:46 CDT 2024