oracle11g - Dynamic sql with pivot/unpivot

Question

I want to flip all rows to columns. So if have following 5 columns with 3 data rows

ACCT_ID       NAME           PHONE              MOBILE      ALTERNATIVE_NAME
01          JOE BROWN        0456-9992-6666    07767828432       ZOE BROWN
02          GILL SHARP       0456-9992-6666    07763928432       BILL SHARP
03          ZAC LOWE         0236-9992-5644    07663925672       LUKE LOWE

I want the result set to look as follows. So have 3 columns and 5 rows.

COL headings (COL1 - COL3) are only added to make clearer, i don't need columns headings

COL1                        COL2                             COL3

01                           02                               03    
JOE BROWN                    GILL SHARP                      ZAC LOWE  
0456-9992-6666               0456-9992-6666                  0236-9992-5644
07767828432                  07763928432                     07663925672
ZOE BROWN                    BILL SHARP                      LUKE LOWE

The following SQL will work if know the acct_id's. But i wont know the acct_id, so need to write some dynamic sql to select the acct_id from table REPORTER.TEMP_PSR_REGION and then do the pivot/unpivot.

CREATE TABLE REPORTER.TEMP_PSR_REGION
  (
     ACCT_ID             VARCHAR(50) NOT NULL,
     NAME                VARCHAR(50) NOT NULL,
     PHONE               VARCHAR(50) NOT NULL,
     MOBILE              VARCHAR(50) NOT NULL,
     ALTERNATIVE_CONTACT VARCHAR(50) NOT NULL
  )

INSERT INTO TEMP_PSR_REGION
            (ACCT_ID,
             NAME,
             PHONE,
             MOBILE,
             ALTERNATIVE_CONTACT)
VALUES     ('01',
            'JOE BROWN',
            '0456-9992-6666',
            '07767828432',
            'ZOE BROWN')
INSERT INTO TEMP_PSR_REGION
            (ACCT_ID,
             NAME,
             PHONE,
             MOBILE,
             ALTERNATIVE_CONTACT)
VALUES     ('02',
            'GILL SHARP',
            '0456-9992-6666',
            '07763928432',
            'BILL SHARP')
INSERT INTO TEMP_PSR_REGION
            (ACCT_ID,
             NAME,
             PHONE,
             MOBILE,
             ALTERNATIVE_CONTACT)
 VALUES     ('03',
            'ZAC LOWE',
            '0236-9992-5644',
            '07663925672',
            'LUKE LOWE')

--- PIVOT/UNPIVOT Example  
select col1, col2, col3
from (
  select t.*, t.acct_id as col_id
  from TEMP_PSR_REGION t
)
unpivot
(
  value FOR heading in (acct_id, name, phone, mobile, ALTERNATIVE_CONTACT)
)
pivot
(
  max(value) for col_id in ('01' as col1, '02' as col2, '03' as col3)                     
)
order by case heading 
 when 'ACCT_ID'             then 1 
 when 'NAME'                then 2 
 when 'PHONE'               then 3
 when 'MOBILE'              then 4 
 when 'ALTERNATIVE_CONTACT' then 5 
 end

What would the dynamic sql look like to do this. I have not written dynamic sql before?

标签： oracle11g

Answer 1

您可以通过查询获得枢轴的IN子句：

select listagg('''' || acct_id || ''' as col' || rownum, ', ') within group (order by acct_id)
from TEMP_PSR_REGION;

LISTAGG(''''||ACCT_ID||'''ASCOL'||ROWNUM,',')WITHINGROUP(ORDERBYACCT_ID)
------------------------------------------------------------------------
'01' as col1, '02' as col2, '03' as col3

然后，您可以将其用作查询的一部分来生成动态 SQL 语句，然后将该语句作为引用游标打开。在此示例中，我使用 SQL*Plus（或 SQL Developer 或 SQLcl）客户端refcursor变量来简化如何声明和 sisplay 它：

var rc refcursor

declare
  l_sql varchar2(32767);
begin
  select 
   q'[
select *
from (
  select t.*, t.acct_id as col_id
  from TEMP_PSR_REGION t
)
unpivot
(
  value FOR heading in (acct_id, name, phone, mobile, ALTERNATIVE_CONTACT)
)
pivot
(
  max(value) for col_id in (]'
|| listagg('''' || acct_id || ''' as col' || rownum, ', ') within group (order by acct_id)
|| q'[)                     
)
order by case heading 
 when 'ACCT_ID'             then 1 
 when 'NAME'                then 2 
 when 'PHONE'               then 3
 when 'MOBILE'              then 4 
 when 'ALTERNATIVE_CONTACT' then 5 
 end]'
into l_sql
from TEMP_PSR_REGION;

  dbms_output.put_line(l_sql); -- for debugging only
  open :rc for l_sql;
end;
/

其中大部分是您的原始查询。本质上，它将您的查询放入字符串变量中，但会发生变化

select col1, col2, col3
from (
...

至

q'[
select *
from (
...]'

尽管您可以根据需要与另一个生成该选择列表listagg()；更重要的是：

...
pivot
(
  max(value) for col_id in ('01' as col1, '02' as col2, '03' as col3)                     
)
...

至：

q'[...
pivot
(
  max(value) for col_id in (]'
|| listagg('''' || acct_id || ''' as col' || rownum, ', ') within group (order by acct_id)
|| q'[)                     
)
...]'

执行该块时，dbms_output调试会显示生成的语句，这与评估过的原始语句相同listagg()：

select *
from (
  select t.*, t.acct_id as col_id
  from TEMP_PSR_REGION t
)
unpivot
(
  value FOR heading in (acct_id, name, phone, mobile, ALTERNATIVE_CONTACT)
)
pivot
(
  max(value) for col_id in ('01' as col1, '02' as col2, '03' as col3)                     
)
order by case heading 
 when 'ACCT_ID'             then 1 
 when 'NAME'                then 2 
 when 'PHONE'               then 3
 when 'MOBILE'              then 4 
 when 'ALTERNATIVE_CONTACT' then 5 
 end

PL/SQL procedure successfully completed.

您可以显示打开的参考光标：

print rc

COL1                                               COL2                                               COL3                                              
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
01                                                 02                                                 03                                                
JOE BROWN                                          GILL SHARP                                         ZAC LOWE                                          
0456-9992-6666                                     0456-9992-6666                                     0236-9992-5644                                    
07767828432                                        07763928432                                        07663925672                                       
ZOE BROWN                                          BILL SHARP                                         LUKE LOWE                                         

使用rownum有点狡猾，因为标题可能是乱序的（因为没有排序），但似乎你并不关心这些。如果确实很重要，您可以使用子查询为基表中的每一行分配一个数字索引，然后使用该值而不是rownum.

您可以将它放在一个返回 ref 游标的函数中，但这取决于您将如何调用它并使用结果。varand只是一个演示，print可能不是您真正会使用的。

您可能还想考虑让一个报告层（如果有的话）处理数据透视。