Home » SQL & PL/SQL » SQL & PL/SQL » row data in a column Format with Table_name and Column Name.
row data in a column Format with Table_name and Column Name. [message #162172] Thu, 09 March 2006 00:57 Go to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hi,

"I want the row data in a column Format with Table_name and Column Name."

Suppose i have a Emp table and Dept table.

HIS3>  desc emp
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)


HIS3>  desc dept
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 DEPTNO                                                NOT NULL NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOC                                                            VARCHAR2(13)

HIS3>  SELECT * FROM EMP;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81      2450                  10
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7839 KING       PRESIDENT           17-NOV-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7900 JAMES      CLERK          7698 03-DEC-81       950                  30
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82      1300

14 rows selected.

HIS3>  select * from dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON


I want a query which gives me something like this type of output -->

SAY i want details for a particular employee with empno =7369

Table 			Column 			Data
----------------------- ----------------------- --------------
EMP			EMPNO			7369
EMP			ENAME			SMITH		
EMP			JOB			CLERK
EMP			MGR			7902
EMP			HIREDATE		17-DEC-80
EMP			SAL			 800
EMP			DEPTNO			20
DEPT			DNAME			RESEARCH
DEPT			LOC			DALLAS



Hope you got it..

But there is one catch .
i want to display only those columns in which the Data is there.
Now take this example , for employee 'SMITH', there is no commission. So that column should not be displayed.



Thanks in Advance,

Amrish KOthari

[mod-edit: applied code tags. Read the second post in the Newbie board about formatting]

[Updated on: Thu, 09 March 2006 03:39] by Moderator

Report message to a moderator

Re: row data in a column Format with Table_name and Column Name. [message #162226 is a reply to message #162172] Thu, 09 March 2006 04:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My first idea was to use the standard method: glue all columns together using a UNION ALL construction. Then I thought of the str2tab, or string_to_table as it is called on my box, TABLE function. Search for it on the board. Here's one link.
Once you've installed it you can use it as follows:
COLUMN column_name  FORMAT A30
COLUMN column_value FORMAT A30
SELECT names.column_name
     , vals.column_value
  FROM ( SELECT ROWNUM rn
              , the_value column_name
           FROM  TABLE(string_to_tab('employee_id,first_name,last_name,email,'||
                                     'phone_number,hire_date,job_id,salary,'||
                                     'commission_pct,manager_id,department_id',',')) column_name
       ) names
     , ( SELECT ROWNUM rn
              , the_value column_value
           FROM TABLE (string_to_tab( ( SELECT TO_CHAR(employee_id)||','||
                                                     first_name||','||
                                                     last_name||','||
                                                     email||','||
                                                     phone_number||','||
                                                     TO_CHAR(hire_date,'DD-MON-YYYY')||','||
                                                     TO_CHAR(job_id)||','||
                                                     TO_CHAR(salary)||','||
                                                     TO_CHAR(commission_pct)||','||
                                                     TO_CHAR(manager_id)||','||
                                                     TO_CHAR(department_id)||','
                                                FROM employees
                                               WHERE employee_id = 190
                            )
                           , ','
                           )
                      ) 
       ) vals
 WHERE vals.rn = names.rn
/
It is not yet 100% what you desired but you have a nice starting point Wink

PS: I've added some code tags to your post. It makes the entire thing a lot easier to read.

MHE
icon7.gif  Re: row data in a column Format with Table_name and Column Name. [message #162235 is a reply to message #162172] Thu, 09 March 2006 05:15 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Thanks Maaher very much
it is working very much fine for one table (Emp)

I will be checking soon for more than 1 table.
DEPT and EMP, both.

In case of any problems , i will surely get back to you.

Thank You,
AMKOTZ

Re: row data in a column Format with Table_name and Column Name. [message #162241 is a reply to message #162172] Thu, 09 March 2006 05:56 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hi Maaher,

There is one problem, the Query what u have provided to me, is having all the columns names is hardcoded.
CAn it be possible somehow that it can be dynamically seleted.
It may be possible that , tomorow i can add or remove some more columns in EMP table .Then in that case i dont want to change the PL/SQL code again.
It will not be maintenance wise effective.
What do you think.


Regds,
AMKOTZ
Re: row data in a column Format with Table_name and Column Name. [message #162243 is a reply to message #162241] Thu, 09 March 2006 06:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you are indeed changing tables all the time I wouldn't like to have you on my team Very Happy. A data model is not a playground. Entire applications are built on top of models, it is not something you change all of the sudden.

But for the sake of the argument: I'll see what I can come up with...

MHE
Re: row data in a column Format with Table_name and Column Name. [message #162249 is a reply to message #162172] Thu, 09 March 2006 06:39 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
HI
Try useing this query where you are creating table from table names.


SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='EMP'
Re: row data in a column Format with Table_name and Column Name. [message #162258 is a reply to message #162249] Thu, 09 March 2006 07:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That would be user_tab_columns or you'd need an OWNER's name, of course Wink. Not only because the same table_name may exist in more than one schema but also from a performance point of view. I deliberately abandoned the UNION ALL method and constructed a select using the table function for the sake of performance.

But the problem here is not getting the column names (XX_TAB_COLUMNS is indeed the most suitable option), but getting a dynamic select containing all columns that have been added or removed after you first constructed the select. In pure SQL there's no way you can accomplish that, I'm afraid. You need to know what you want to select before you can actually select it. It seems logical, doesn't it. In PL/SQL you could write a NDS (Native Dynamic SQL) statement doing the fetch for you and returning an array or you could write a function returning a ref cursor.

MHE
Re: row data in a column Format with Table_name and Column Name. [message #162977 is a reply to message #162172] Tue, 14 March 2006 06:51 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
hi maaher,
i have one problem.
The function what you have given will work fine if the data passed to the function is one row.
But according to my query , it returns more than one row.

CAn it be done in one procedure.

the query is :

HIS3> create or replace view vw as
2 SELECT names.column_name
3 , vals.column_value
4 FROM ( SELECT ROWNUM rn
5 , the_value column_name
6 FROM TABLE(string_to_tab('CONDITION',',')) column_name
7 ) names
8 , ( SELECT ROWNUM rn
9 , the_value column_value
10 FROM TABLE (string_to_tab( ( SELECT HPM.CONDITION||','
11 FROM
12 HIS_PAV_MODUL HPM
13 WHERE
14 HPM.hps_id = 567901
15 and upper(HPM.CONDITION) like upper('%Analog_inputs_d%')
16 )
17 , ',')
18 )
19 ) vals
20 WHERE vals.rn = names.rn
21 /

View created.

HIS3> SELECT * FROM VW;
SELECT * FROM VW
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


Thanks in advance.
AMKOTZ
Re: row data in a column Format with Table_name and Column Name. [message #163101 is a reply to message #162172] Tue, 14 March 2006 22:51 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hi maaher,
what i mean to say is this:


SELECT names.column_name
, vals.column_value
FROM ( SELECT ROWNUM rn
, the_value column_name
FROM TABLE(string_to_tab('ename',',')) column_name
) names
, ( SELECT ROWNUM rn
, the_value column_value
FROM TABLE (string_to_tab( ( SELECT ename||','
FROM
emp
WHERE

upper(ename) like upper('%m%')
)
, ',')
)
) vals
WHERE vals.rn = names.rn
/

The result is :

ERROR at line 9:
ORA-01427: single-row subquery returns more than one row

I have a query which returns me more than one row.

Regds,
AMKOTZ
Re: row data in a column Format with Table_name and Column Name. [message #163149 is a reply to message #162172] Wed, 15 March 2006 02:10 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
i came to know one more idea of using cursors in the function which u gave.
can we modify the existing function for cursors or write another function and use something like this:

SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));
Re: row data in a column Format with Table_name and Column Name. [message #163206 is a reply to message #163149] Wed, 15 March 2006 08:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hi, I'm back. Sorry for the late reply. I've been looking at your problem again and I came up with The following:
  • The idea behind the script was: concatenate all the values and decompose this string to get rows. If you have more than one row in the select constructing the string, you have to concatenate this string too. Therefor I've used another handy function called 'concat_all'. The source is on the board on several places.
  • Then I modified the join condition of the names and the values so it will join no matter how many rows you get.
  • I also changed the names query: now I've used, like discussed before, USER_TAB_COLUMNS.

This query has one major drawback: the size of a concat_all expression cannot exceed 4000 bytes.

Perhaps a UNION ALL is not that bad after all...

Here it is:
SELECT names.column_name
     , vals.column_value
  FROM ( SELECT ROWNUM rn
              , column_name
           FROM user_tab_columns
          WHERE table_name in ('EMPLOYEES','DEPARTMENTS')
          ORDER BY table_name, column_id
        ) names
     , ( SELECT ROWNUM rn
              , the_value column_value
           FROM TABLE (string_to_tab( ( SELECT concat_all( concat_expr(TO_CHAR(e.employee_id)||','||
                                                     e.first_name||','||
                                                     e.last_name||','||
                                                     e.email||','||
                                                     e.phone_number||','||
                                                     TO_CHAR(e.hire_date,'DD-MON-YYYY')||','||
                                                     TO_CHAR(e.job_id)||','||
                                                     TO_CHAR(e.salary)||','||
                                                     TO_CHAR(e.commission_pct)||','||
                                                     TO_CHAR(e.manager_id)||','||
                                                     TO_CHAR(e.department_id)||','||
                                                     TO_CHAR(d.department_id)||','||
                                                     d.department_name||','||
                                                     TO_CHAR(d.manager_id)||','||
                                                     TO_CHAR(d.location_id),','))
                                                FROM employees e
                                                   , departments d
                                               WHERE last_name like '%E%'
                                                 and d.department_id = e.department_id
                                      )
                                    , ','
                                    )
                      )
       ) vals
 WHERE decode(mod(vals.rn,15),0,15,mod(vals.rn,15)) = names.rn
/


MHE

PS: I've closed your other thread.
Re: row data in a column Format with Table_name and Column Name. [message #163490 is a reply to message #163206] Thu, 16 March 2006 21:29 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hello Maaher,
Not at all problem with the late reply.
Well thanks for the reply.
The query what you have given is perfectly fine.Not a problem.

Now the CONCAT_ALL function method is very much complicated.But the functionality works fine.

But i am thinking the maintenance of the query is very complicated.(I am becoming a bit NOSTALGIC here Razz

If UNION_ALL is used can we eliminate the need of function CONCAT_ALL.??

I also have one query with me which print table.
the query is :

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/


Now when i use this procedure:

HIS3> set serveroutput on
HIS3> begin
2 print_table('select * from emp');
3 end;
4 /
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE : 17-dec-1980 00:00:00
SAL : 800
COMM :
DEPTNO : 20
-----------------
EMPNO : 7499
ENAME : ALLEN
JOB : SALESMAN
MGR : 7698
HIREDATE : 20-feb-1981 00:00:00
SAL : 1600
COMM : 300
DEPTNO : 30
-----------------
EMPNO : 7521
ENAME : WARD
JOB : SALESMAN
MGR : 7698
HIREDATE : 22-feb-1981 00:00:00
SAL : 1250
COMM : 500
DEPTNO : 30
-----------------
...

Now my question is whether it is possible to use this query instead of CONCAT_ALL.
What i am planning to do is in the end of the above procedure , i will remove DOPL and the two columns i will get.Can i put this into a pl/sql table ?
Will this work?

Bye,
Amkotz

[Updated on: Thu, 16 March 2006 23:26]

Report message to a moderator

Re: row data in a column Format with Table_name and Column Name. [message #163539 is a reply to message #163490] Fri, 17 March 2006 03:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes, you're right. If you would use a UNION ALL construction you would no longer need CONCAT_ALL or string_to_tab. I was looking for a pure SQL solution and I was a bit brave with my concat_all solution (although it does outperform a UNION ALL since it scans a table only once). Let's just abandon that road, especially since it is limited to 4000bytes in total. That is just not acceptable to output.

What are you planning to do with it? Have you tried this:
set long 2000000
set newpage none
set head off
set termout off
set feedb off
set verify off

spool C:\yourdestination\&1..xml
select dbms_xmlgen.getxml('select * from &1') from dual;
spool off

set feedb on
set termout on
set verify on
set head on
set newpage 1
It spools the output to an XML file. This file can easily be transformed to any format you'd like.

MHE
Re: row data in a column Format with Table_name and Column Name. [message #163697 is a reply to message #163539] Sat, 18 March 2006 05:20 Go to previous messageGo to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hi maaher,
Thanks for the innovative reply.
that is also working , Bt finally i am using Dynamic sql , the Procedure which i have posted below.I just passed the query .

The limitation of 4000 bytes can be a big issue.
The UNION ALL query will be very heavy for the Database.


Thanks,
AMKOTZ
Re: row data in a column Format with Table_name and Column Name. [message #201878 is a reply to message #163539] Tue, 07 November 2006 03:05 Go to previous message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hello Maaher,

After a long time.Indeed.

I was just looking into the XML style of formatting using dbms_xmlgen.getxml.

You said we can transform the file in any format we would like.
Now i am looking into that part of it.
Can you throw some light into it ?

Why i had started this again , because in my design for converting a Row Data into Column Data is giving very low performance. The base tables have grown very big.I am worried. Embarassed

If you say i can send u the design style what we are using and you can give your valuable ideas on how to improve.
That is the reason why i was looking into XML format and other details.



Thanks in Advance,
Amkotz

[Updated on: Tue, 07 November 2006 03:14]

Report message to a moderator

Previous Topic: ORA-04052
Next Topic: Picking one language
Goto Forum:
  


Current Time: Tue Dec 06 00:07:54 CST 2016

Total time taken to generate the page: 0.13386 seconds