4

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.

  • 2
    Do you know the max depth? – Anthony Genovese Apr 13 '18 at 22:22
  • @Anthony, He said that it is unknown. But I guess you could find it out on the Fly doing sth like MAX and a Group by indiv_id combination. – Magier Apr 13 '18 at 22:34
  • @Magier that's what I was thinking too. – Anthony Genovese Apr 13 '18 at 23:31
  • It is unknown. I've seen as many as 10 but I haven't run a scan because the data set is 299 million records. – berlin Apr 16 '18 at 0:41
0

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> 
  • 1
    Wow, thanks for such a complete walk through. This is amazing. I ended up using your first method because I could follow the logic and could decide that more than 10 linked IDs didn't matter. – berlin Apr 16 '18 at 20:52
0

PostgreSQL

PostgreSQL provides the SQL specs

With these two features, this is substantially easier and cleaner (and likely faster)

SELECT hhid, array_agg(indiv_id ORDER BY prim='Y' DESC, indiv_id)
FROM t
GROUP BY hhid;
 hhid |   array_agg   
------+---------------
 A    | {10,11,12,13}
 B    | {15,14}
 C    | {16,17}
(3 rows)
  • 1
    Without going into the speed element ... This is very useful as I run PostgreSQL at home on a similar data set. Not completely on-point to the OP but still a great tutorial. – berlin Apr 16 '18 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.