Kyle

• Oct 26th, 2006

 

there is already a table with all the column names for a table in rows. user_tab_cols.select table_name, column_name from user_tab_cols where table_name = 'your table';It should give you the result you want.

prabu

• Oct 27th, 2006

 

Hi friends, We have to change the column into row like this.It is a pivoting table . For exampleDEPTNO COUNT(*)------- ---------- 10 3 20 5 30 4 40 DEPT_10 DEPT_20 DEPT_30 DEPT_40------- ---------- ---------- ---------- 3 5 4 select MAX( decode( deptno, 10, cnt, null ) ) dept_10, MAX( decode( deptno, 20, cnt, null ) ) dept_20, MAX( decode( deptno, 30, cnt, null ) ) dept_30, MAX( decode( deptno, 40, cnt, null ) ) dept_40 from ( select deptno, count(*) cnt from emp group by deptno ) group by deptno

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 3rd, 2006

 

select deptno, count(*) no_emp from emp group by deptno;

    this will give you like

deptno          no_emp

10                  4

20                   5

...........

now to put this result in a single row use:

select count(decode(deptno,10, deptno, null)) No_empof_10dept,

count(decode(deptno,20,deptno,null)) no_empof_20dept,

count(decode(deptno,10,null, 20 , null, deptno)) otherthen_10_20

from emp;

ora.nachs

 Profile Answers by ora.nachs 

• Feb 5th, 2007

 

we can also do this by insert all statement.....INSERT ALLINTO sales_info VALUES (employee_id,week_id,sales_MON)INTO sales_info VALUES (employee_id,week_id,sales_TUE)INTO sales_info VALUES (employee_id,week_id,sales_WED)INTO sales_info VALUES (employee_id,week_id,sales_THUR)INTO sales_info VALUES (employee_id,week_id, sales_FRI)SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,sales_WED, sales_THUR,sales_FRIFROM sales_source_data;this is exactly called as pivoting insert

sreekumar_nair_it

 Profile Answers by sreekumar_nair_it Questions by sreekumar_nair_it

• Nov 30th, 2007

 

Try this

CREATE OR REPLACE FUNCTION FIN2008.rowtocol( p_slct IN VARCHAR2,

p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2

AUTHID CURRENT_USER AS

TYPE c_refcur IS REF CURSOR;

lc_str VARCHAR2(4000);

lc_colval VARCHAR2(4000);

c_dummy c_refcur;

l number;

BEGIN

OPEN c_dummy FOR p_slct;

LOOP

FETCH c_dummy INTO lc_colval;

EXIT WHEN c_dummy%NOTFOUND;

lc_str := lc_str || p_dlmtr || lc_colval;

END LOOP;

CLOSE c_dummy;

RETURN SUBSTR(lc_str,2);

EXCEPTION

WHEN OTHERS THEN

lc_str := SQLERRM;

IF c_dummy%ISOPEN THEN

CLOSE c_dummy;

END IF;

RETURN lc_str;

END;

/


First parameter is your query and the second parameter is delimiter