General - dear friendi want to know that in a table how to change the column to row.

GeekInterview.com > Interview Questions > Oracle > General

Go To First | Previous Question | Next Question

General | Question 206 of 211

dear friend

i want to know that in a table how to change the column to row.

Total Answers and Comments: 5

Last Update: December 01, 2007 Asked by: dillip sahu

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

October 26, 2006 21:46:06

Kyle

RE: dear friendi want to know that in a t...

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.

Is this answer useful? Yes | No

October 27, 2006 01:47:22

prabu

RE: dear friendi want to know that in a t...

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

Is this answer useful? Yes | No

December 03, 2006 06:01:54

rampratap409

Member Since: September 2006 Contribution: 111

RE: dear friendi want to know that in a t...

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;

Is this answer useful? Yes | No

February 05, 2007 05:53:41

ora.nachs

Member Since: January 2007 Contribution: 9

RE: dear friendi want to know that in a t...

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

Is this answer useful? Yes | No

November 30, 2007 23:47:30

sreekumar_nair_it

Member Since: November 2007 Contribution: 67

RE: dear friendi want to know that in a table how to change the column to row.

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank