Add column for subsequent values for a record 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.
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   &lt;null&gt;   &lt;null&gt; 
C        16   17   &lt;null&gt;   &lt;null&gt; 


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
&lt;PivotSet&gt;&lt;item&gt;&lt;column name = "RN"&gt;1&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;10&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "RN"
&gt;2&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;11&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "RN"&gt;3&lt;/column&gt;&lt;column name = "MIN(INDI
V_ID)"&gt;12&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "RN"&gt;4&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;13&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet
&gt;

B
&lt;PivotSet&gt;&lt;item&gt;&lt;column name = "RN"&gt;1&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;15&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "RN"
&gt;2&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;14&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt;

C
&lt;PivotSet&gt;&lt;item&gt;&lt;column name = "RN"&gt;1&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;16&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "RN"
&gt;2&lt;/column&gt;&lt;column name = "MIN(INDIV_ID)"&gt;17&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt;


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 &lt;null&gt;     &lt;null&gt;    
C         16         17 &lt;null&gt;     &lt;null&gt;