為記錄的後續值添加列

我有一個結構如下的表：

CREATE TABLE t (
 HHID      varchar2(1),
 INDIV_ID  int,
 PRIM      varchar2(1)
);

insert into t values ('A',       10,       'Y');
insert into t values ('A',       11,       'N');
insert into t values ('A',       12,       'N');
insert into t values ('A',       13,       'N');
insert into t values ('B',       14,       'N');
insert into t values ('B',       15,       'Y');
insert into t values ('C',       16,       'Y');
insert into t values ('C',       17,       'N');

我需要將其轉換為以下內容：

HHID    PRIMARY   SECOND  THIRD  FOURTH FIFTH ....
A       10        11      12     13
B       15        14
C       16        17

大問題是可能添加的列的數量是未知的，我只需要主要的 INDIV_ID 顯示為主要的，並且在後續列中擷取任何附加資訊。

我現在看到的每個答案都只建議將行 id 簡單地轉換為列 ID 的情況的答案，但是，我還沒有看到一個可以將相同 HHID 的後續記錄作為附加列放置的樞軸.

要首先獲取主值，然後按 indiv_id 獲取值，請為每行分配一個從 1 開始的每個 hhid 的數字：

row_number() over (
 partition by hhid order by prim desc, indiv_id
) rn

一旦你得到了它，你就可以通過值 1、2、3、…、N 進行旋轉。

with rws as (
 select hhid, indiv_id, 
        row_number() over (
          partition by hhid order by prim desc, indiv_id
        ) rn
 from   t
)
 select * from rws
 pivot (
   min(indiv_id) for rn in (
     1, 2, 3, 4
   )
 );

HHID   1    2    3        4        
A        10   11       12       13 
B        15   14   <null>   <null> 
C        16   17   <null>   <null> 

如果您知道（或可以做出合理的猜測……）關於最大行數/隱藏，您就完成了。如果您希望輸出中的列發生變化，則沒有簡單的解決方案。

您可以 XML 透視：

with rws as (
 select hhid, indiv_id, 
        row_number() over (
          partition by hhid order by prim desc, indiv_id
        ) rn
 from   t
)
 select * from rws
 pivot xml (
   min(indiv_id) for rn in ( any )
 );

H
-
RN_XML
------------------------------------------------------------------------------------------------------------------------
A
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">10</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">11</column></item><item><column name = "RN">3</column><column name = "MIN(INDI
V_ID)">12</column></item><item><column name = "RN">4</column><column name = "MIN(INDIV_ID)">13</column></item></PivotSet
>

B
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">15</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">14</column></item></PivotSet>

C
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">16</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">17</column></item></PivotSet>

但這有點作弊，因為您仍然有一個專欄。你需要解析 XML ……

或者您可以使用動態 SQL：

var cur refcursor;
declare
 in_list varchar2(10);
begin
 with rws as (
   select distinct row_number() over (
            partition by hhid order by prim desc, indiv_id
          ) rn
   from   t
 )
   select listagg(rn, ',') within group (order by rn) 
   into   in_list
   from   rws;

 open :cur for 'with rws as (
 select hhid, indiv_id, 
        row_number() over (
          partition by hhid order by prim desc, indiv_id
        ) rn
 from   t
)
 select * from rws
 pivot (
   min(indiv_id) for rn in ( ' || in_list || ' )
 )';

end;
/

print :cur;

H          1          2          3          4
- ---------- ---------- ---------- ----------
A         10         11         12         13
B         15         14 <null>     <null>    
C         16         17 <null>     <null> 

引用自：https://dba.stackexchange.com/questions/203914