最近のトラックバック

最近のコメント

2017年3月
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  
無料ブログはココログ

« Googleの技術ドキュメント日本語化 | トップページ | 行移行と行連鎖 »

2008年6月11日 (水)

行(レコード)を列(カラム)として表示

これも、行(レコード)を列(カラム)として表示するやり方。
ありがとうTom!

Ask Tom "Column to rows"

質問をした人は、こんなふうにUNIONするやり方を紹介。
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

一方Tomの回答は、

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
....

« Googleの技術ドキュメント日本語化 | トップページ | 行移行と行連鎖 »

コメント

コメントを書く

(ウェブ上には掲載しません)

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/181317/41504193

この記事へのトラックバック一覧です: 行(レコード)を列(カラム)として表示:

« Googleの技術ドキュメント日本語化 | トップページ | 行移行と行連鎖 »