Thanks for the question, Koshal.
Asked: June 09, 2008 - 11:53 pm UTC
Last updated: June 10, 2008 - 9:18 am UTC
Version: 10.0.3.0
Viewed 1000+ times
You Asked
Hi
What are the possible ways we can rewrite the above query in 10g (without using pl/sql). I believe in 11g we can use pivot/unpivot. I am not worried abt performance, I am worried abt the length of the code. If I have 100 fields I will be having 100+ lines of code.
CREATE TABLE rows_to_column AS
SELECT * FROM (
SELECT field_cnst, field_1 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_2 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_3 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_4 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_5 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_6 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_7 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_8 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_9 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_10 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_11 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_12 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_13 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_14 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_15 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_16 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_17 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_18 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_19 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) UNION ALL
SELECT field_cnst, field_20 FROM table1 t1 WHERE field_cnst IN (SELECT fnm_ln_id FROM table2) ) src
Thanks
Koshal
and Tom said...
.... I am worried abt the length of the code. ...
well, that is just silly. who cares are the "length" of something. If you are no pleased with the "length", just hide it in a view. given that it is such rote code - you would write a script to write the sql.
but, I will say, your approach above is highly inefficient, we can do this in a single pass on the table. And your subject - which was "rows to column" was backwards - it is columns to rows - you are taking a bad data model, whereby someone has stored attributes in record that obviously were meant to be stored in rows.
scott%ORA10GR2> create or replace view vw
2 as
3 select to_char(empno) f1,
4 ename f2,
5 to_char(hiredate) f3,
6 to_char(sal) f4,
7 to_char(comm) f5,
8 job f6,
9 deptno field_cnst
10 from scott.emp;
View created.
scott%ORA10GR2>
scott%ORA10GR2> with data
2 as
3 (select level C from dual connect by level <= 6)
4 select case C when 1 then f1 when 2 then f2 when 3 then f3 when 4 then f4 when 5 then f5 when 6 then f6 end val
5 from vw, data
6 where field_cnst in (select deptno from scott.dept)
7 /
VAL
----------------------------------------
ANALYST
20182.53
09-DEC-82
SCOTT
7788
CLERK
800
17-DEC-80
SMITH
7369
SALESMAN
300
1600
20-FEB-81
ALLEN
7499
SALESMAN
500
....
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment