OraFAQ Forum: SQL & PL/SQL » Display the columns of a table into rows delimited by comma

Home » SQL & PL/SQL » SQL & PL/SQL » Display the columns of a table into rows delimited by comma (11.2.0.4)

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

Display the columns of a table into rows delimited by comma [message #651467]

Wed, 18 May 2016 08:14

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hello,

I have one requirement as below.

1. Table name is the input parameter
2. Table name column names delimited by comma so that I can retrieve the data.

Please advice.

select empno,ename,sal from emp;

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651468 is a reply to message #651467]

Wed, 18 May 2016 08:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>so that I can retrieve the data.
retrieve data to where exactly?

SQL must be known & static at compile time
You will need to (ab)use EXECUTE IMMEDIATE.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651477 is a reply to message #651467]

Wed, 18 May 2016 10:53

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this what you want:

SQL> select listagg(column_name,',') within group (order by column_id) cols
  2  from user_tab_columns where table_name='&table_name';
Enter value for table_name: EMP
COLS
----------------------------------------------------------------------------
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

1 row selected.

SQL> /
Enter value for table_name: DEPT
COLS
----------------------------------------------------------------------------
DEPTNO,DNAME,LOC

1 row selected.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651481 is a reply to message #651477]

Wed, 18 May 2016 22:52

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you Michel.

I want the output as below.

SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP;

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651483 is a reply to message #651481]

Thu, 19 May 2016 00:05

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Something like this?

SQL> set colsep ,
SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;

     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE,       SAL,      COMM,    DEPTNO
----------,----------,---------,----------,--------,----------,----------,----------
      7369,SMITH     ,CLERK    ,      7902,17.12.80,      1600,          ,        20
      7499,ALLEN     ,SALESMAN ,      7698,20.02.81,      2400,       300,        30
      7521,WARD      ,SALESMAN ,      7698,22.02.81,      2050,       500,        30

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651485 is a reply to message #651481]

Thu, 19 May 2016 00:33

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So just concatenate (||) 'SELECT ' and 'FROM...' before and after listagg.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651501 is a reply to message #651485]

Thu, 19 May 2016 04:16

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you cadot and Littlefoot.

I have tried and working now.As of now it is fine. I will check with my requirement if any change.

select  'SELECT ' || listagg(column_name,',') within group(order by column_id) || ' FROM ' || '&tablename;' cols
  from user_tab_columns where table_name='&table_name';

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651507 is a reply to message #651501]

Thu, 19 May 2016 05:40

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

My requirement is the below query has to run and retrieve the output..Please provide the solution.

select  'SELECT ' || listagg(column_name,',') within group(order by column_id) || ' FROM ' || '&tablename;' cols
  from user_tab_columns where table_name='&table_name';

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651508 is a reply to message #651507]

Thu, 19 May 2016 05:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Spool it in a file and execute the file.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651515 is a reply to message #651508]

Thu, 19 May 2016 06:27

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Is there any other way to do it to run directly

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651520 is a reply to message #651515]

Thu, 19 May 2016 07:23

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

This seems to me to be a really bogus "requirement". You are jumping through a lot of hoops just to get to 'SELECT * ' from any given table.
Step back from the techiniqe and describe the business requirement. How is this to be used? Where do the inputs come from? Where to the outputs go? Why is the table name not known in advance?

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651521 is a reply to message #651515]

Thu, 19 May 2016 07:23

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

This is the most bizarre requirement I've seen in some time. What's the real problem?

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651523 is a reply to message #651515]

Thu, 19 May 2016 07:51

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

grpatwari wrote on Thu, 19 May 2016 13:27

Is there any other way to do it to run directly

Have a look at T.Kyte's print_table function.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651577 is a reply to message #651523]

Fri, 20 May 2016 05:15

Kumar876
Messages: 1
Registered: April 2014

Junior Member

Hi,

I have modified one of the query posted in this page to the below format.
It accepts &table_name as input param.
If a table_name is not passed, then this query will run for all the tables.


SELECT  'SELECT ' || listagg(column_name,',') within group(order by column_id) || ' FROM ' || MAX(TABLE_NAME) ||';' cols
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = NVL('&table_name',TABLE_NAME)
GROUP BY TABLE_NAME;

Please note that - there is possibility of getting the below error while using LISTAGG when list being created is too long

ORA-01489: result of string concatenation is too long  error

I am a silent member in this forum and trying to learn technical concepts from the experts in this forum.
Please correct any mistakes.

Thanks,
Kumar

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651578 is a reply to message #651523]

Fri, 20 May 2016 05:47

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Generally table will be passed from application.Need to generate the query and execute the query based on the provided table name.This is the exact requirement.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651583 is a reply to message #651578]

Fri, 20 May 2016 07:09

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

grpatwari wrote on Fri, 20 May 2016 05:47

Generally table will be passed from application.Need to generate the query and execute the query based on the provided table name.This is the exact requirement.

I have to agree with Roachcoach. This is a bizarre requirement. It is starting to smell suspiciously like 'entity-value pairs'. See Bad Carma

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651586 is a reply to message #651583]

Fri, 20 May 2016 08:32

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You can pass a select and then execute it using execute immediate but this is such a dangerous application. What happens if someone inserts something like DROP TABLE EMP instead of SELECT COL1,COL2,COL3 FROM EMP This is a really bad idea

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651587 is a reply to message #651586]

Fri, 20 May 2016 08:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This is the exact requirement.
Somebody needs to be (re)trained in requirements writing.
This qualifies at Worst Practice & a crime against data.

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651590 is a reply to message #651578]

Fri, 20 May 2016 09:33

flyboy
Messages: 1903
Registered: November 2006

Senior Member

grpatwari wrote on Fri, 20 May 2016 12:47

Generally table will be passed from application.Need to generate the query and execute the query based on the provided table name.This is the exact requirement.

Shall the result set be treated anyhow? I see no remark about in that requirement nothing about it, so I doubt what is the purpose behind it. Just arbitrarily run some queries to make DB process random data?

(you already were asked by Ed where should that output go, but I do not see any attempt to answer this question)

Report message to a moderator

Re: Display the columns of a table into rows delimited by comma [message #651592 is a reply to message #651577]

Fri, 20 May 2016 09:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Kumar876 wrote on Fri, 20 May 2016 12:15

...
Please note that - there is possibility of getting the below error while using LISTAGG when list being created is too long
ORA-01489: result of string concatenation is too long  error
...

The result of LISTAGG is limited to 4000 bytes. If you have many columns in a table the result could exceed this value.

Report message to a moderator

Previous Topic:	Pattern Matching for Multivalue
Next Topic:	IN (or) Equality Operator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 15:20:00 CDT 2024