Oracle行転記pivot、列転記unpivotのSql文のまとめ

参考記事:http://blog.csdn.net/tianlesoftware/article/details/4704858
複数行の文字列
これは比較的簡単で,‖またはconcat関数で実現できる

select concat(id,username) str from app_user

select id||username str from app_user

文字列が複数の列に回転するのは実際には文字列を分割する問題であり、substrを使用することができる.
、instr、regexp_substr関数方式
文字列を複数行に変換
union allの使用
関数などの方式
wm_concat関数
まずこの不思議な関数wmを見てみましょうconcat(カラム名)、カラム値を「,」で区切って1行に表示できる関数です.次に、この不思議な関数がテストデータの準備にどのように適用されるかを見てみましょう.

create table test(id number,name varchar2(20));

insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');

効果1:行は列を回転し、デフォルトのカンマは区切ります

select wm_concat(name) name from test;

効果2:結果のカンマを「|」に置き換える

select replace(wm_concat(name),',','|') from test;

効果3:IDグループでnameをマージ

select id,wm_concat(name) name from test group by id;

sql文は次のsql文に等しい

--------     ：8i,9i,10g       （ MAX + DECODE ）
select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ','||name, null)) || max(decode(rn, 3, ','||name, null)) str
    from (select id, name ,row_number() over(partition by id order by name) as rn from test) t group by id order by 1; 
     
--------     ：8i,9i,10g      （ ROW_NUMBER + LEAD ）
select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1)
    over(partition by id order by name) ||  lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3) 
    over(partition by id order by name) as str from test) where rn = 1 order by 1;
  
--------     ：10g      （ MODEL ）
select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number()
    over(partition by id order by name) as rn) measures (cast(name as varchar2(20)) as str) rules upsert iterate(3)
    until(presentv(str[iteration_number + 2], 1, 0)=0) (str[0] = str[0] || ',' || str[iteration_number + 1]) order by 1;     
         
--------     ：8i,9i,10g      （ MAX + DECODE ）
select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str from (select id, name, row_number() 
    over(partition by id order by name) rn from test) t start with rn = 1 connect by rn = prior rn + 1 and id = prior id
    group by t.id;

怠け者の拡張法:
ケース:「create or replace view as selectフィールド1,...フィールド50 from tablename」のようなビューを書きます.ベーステーブルには50以上のフィールドがありますが、手書きで書くのは面倒ですが、簡単な方法はありませんか.もちろんありますwm_を適用すればこのニーズを簡単にするために
仮に私のAPP_USERテーブルには(id,username,password,age)の4つのフィールドがあります.クエリの結果は次のとおりです.

 /**              */
select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr 
       from user_tab_columns where table_name='APP_USER';  

システムテーブル方式によるクエリー

select * from user_tab_columns

Oracle 11 g行列交換pivotとunpivotの説明
Oracle 11 gでは、pivot(行転列)とunpivot(列転行)の2つのクエリーが追加されました.
参照先:http://blog.csdn.net/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
Googleでは、ネット上に比較的詳細なドキュメントがあります.http://www.oracle-developer.net/display.php?id=506
pivot列移行
テストデータ(id、タイプ名、販売数)、ケース:果物のタイプに基づいてデータを検索し、各タイプの販売数を表示します.

create table demo(id int,name varchar(20),nums int);  ----    
insert into demo values(1, '  ', 1000);
insert into demo values(2, '  ', 2000);
insert into demo values(3, '  ', 4000);
insert into demo values(4, '  ', 5000);
insert into demo values(5, '  ', 3000);
insert into demo values(6, '  ', 3500);
insert into demo values(7, '  ', 4200);
insert into demo values(8, '  ', 5500);

グループ・クエリー(もちろん、データのクエリーの要件に合致しません)

select name, sum(nums) nums from demo group by name

行と列の問合せ

select * from (select name, nums from demo) pivot (sum(nums) for name in ('  '   , '  ', '  ', '  '));

注:pivot(集約関数for列名in(タイプ))では、in(')では別名を指定できます.inではselect distinct code from customersなどのサブクエリを指定できます.
もちろんpivot関数を使わなくても、以下の文に等しいが、コードが長いので理解しやすい

------      
select * from （select sum(nums)    from demo where name='  '）,（select sum(nums)    from demo where name='  '）,
       （select sum(nums)    from demo where name='  '）,（select sum(nums)    from demo where name='  '）;
       
------  decode     
select sum(decode(name,'  ',nums))   , sum(decode(name,'  ',nums))   , 
       sum(decode(name,'  ',nums))   , sum(decode(name,'  ',nums))    from demo

unpivot行転列は、その名の通り複数列を1列に変換することです
ケース:現在、4四半期の販売数を記録した果物表があり、各果物の四半期ごとの販売状況を複数行のデータで表示します.
テーブルとデータの作成

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);

insert into Fruit values(1,'  ',1000,2000,3300,5000);
insert into Fruit values(2,'  ',3000,3000,3200,1500);
insert into Fruit values(3,'  ',2500,3500,2200,2500);
insert into Fruit values(4,'  ',1500,2500,1200,3500);
select * from Fruit

列転行クエリー

select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

注:unpivotには集約関数がなく、xiaoshou、jiduフィールドも一時的な変数です.

同じunpivotを使わなくても同じ効果が得られますが、sql文は長く、実行速度も前者ほど高くありません.

select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f

XMLタイプ
上記pivot列の移行例では、クエリーが必要なタイプを知っていますが、in()で含めると、どのような値があるか分からない場合は、クエリーをどのように構築しますか?
この問題を解決するために、pivotオペレーションの別のサブ文XMLを使用することができる.この句を使用すると、XML形式でpivot操作を実行した出力を作成できます.この出力では、文字値ではなく特殊な句ANYを指定できます.
例は次のとおりです.

select * from (
   select name, nums as "Purchase Frequency"
   from demo t
)                              
pivot xml (
   sum(nums) for name in (any)
)

ご覧の通り、列NAME_XMLはXMLTYPEで、ルート要素はです.各値は、名前-値要素のペアで表されます.任意のXMLアナライザの出力を使用して、より有用な出力を生成できます.

結論
PivotはSQL言語に非常に重要で実用的な機能を追加しました.pivot関数を使用して、decode関数を多く含む難解で直感的でないコードを記述することなく、任意の関係テーブルに対してクロステーブルレポートを作成できます.同様に、unpivot操作を使用してクロステーブル・レポートを変換し、通常のリレーショナル・テーブルとして保存できます.Pivotは、通常のテキストまたはXML形式の出力を生成することができる.XML形式の出力の場合、pivot操作で検索する値ドメインを指定する必要はありません.