神刀安全网

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, notmultiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations

SELECT 'SCOTT' login_id    ,'X' alpha, 'Y' beta   ,10 catgy1   ,20 catgy2   ,30 catgy3   ,40 catgy4   ,50 catgy5   ,60 catgy6 FROM dual /  LOGIN A B     CATGY1     CATGY2     CATGY3     CATGY4     CATGY5     CATGY6 ----- - - ---------- ---------- ---------- ---------- ---------- ---------- SCOTT X Y         10         20         30         40         50         60   1 row selected

Trouble is, I needed the categories described as rows, not columns. So I wrapped the original query within an unpivot , commenting how the syntax represents the translation.

select login_id, alpha, beta, catgy, quota -- new columns from ( -- existing query   SELECT 'SCOTT' login_id        ,'X' alpha, 'Y' beta             ,10 catgy1       ,20 catgy2       ,30 catgy3       ,40 catgy4       ,50 catgy5       ,60 catgy6   FROM dual ) -- end existing query unpivot ( quota -- new column: value   for catgy in -- new column translating previously separate columns to discrete data     ( -- and the column -> data translation listed here      catgy1 as 'CATGY1'      ,catgy2 as 'CATGY2'     ,catgy3 as 'CATGY3'     ,catgy4 as 'CATGY4'     ,catgy5 as 'CATGY5'      ,catgy6 as 'CATGY6'     ) ) /  LOGIN A B CATGY       QUOTA ----- - - ------ ---------- SCOTT X Y CATGY1         10 SCOTT X Y CATGY2         20 SCOTT X Y CATGY3         30 SCOTT X Y CATGY4         40 SCOTT X Y CATGY5         50 SCOTT X Y CATGY6         60   6 rows selected

Relatively easy! Hope it helps one day.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Simple Unpivot

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮