Add column for subsequent values for a record

dynamic-sqloraclepivotplsql

I have a table structured as follows:

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');

What I need is to convert this to the following:

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

Big issue is that the number of possible added columns is unknown, I just need the primary INDIV_ID to show up as the primary and any additionals to be capture in subsequent columns.

Every answer I've looked at right now only suggests answers for the case for a simple pivot of a row id to the column ID however, I haven't seen a pivot that manages to place subsequent records of the same HHID as an additional column.

Best Answer

To get the primary value first, then the values by indiv_id, assign each row a number starting at 1 for each hhid:

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

Once you've got that, you pivot by the values 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>

If you know (or can make a reasonable guess...) as to the max number of rows/hhid you're done. If you want the columns in the output to change, there's no simple solution.

You could XML pivot:

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>

But that's kinda cheating as you still have one column. And you need to parse the XML...

Or you can fall back on dynamic 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>
Related Question