Forum Stats

  • 3,839,777 Users
  • 2,262,536 Discussions
  • 7,901,054 Comments

Discussions

dynamic pivot: generate static columns from uknown records

User_2SRRN
User_2SRRN Member Posts: 12 Green Ribbon

Hi,

Is it even possible to create something like this using SQL?

create table pvt_test

(

custId int,

custName varchar(20),

someValue int,

someText varchar(29)

);


insert into pvt_test (custId, custName, someValue, someText) values (1, 'Joe', 100, 'value 300');

insert into pvt_test (custId, custName, someValue, someText) values (1, 'Joe', 200, 'value 300');

insert into pvt_test (custId, custName, someValue, someText) values (1, 'Joe', 300, 'value 300');


insert into pvt_test (custId, custName, someValue, someText) values (2, 'Sara', 100, 'value 100');

insert into pvt_test (custId, custName, someValue, someText) values (2, 'Sara', 150, 'value 150');


select *

from pvt_test



-- desired output (table):

-- custId, custName, someValue_1, someValue_2, someValue_3, someValue_4, someText_1, someText_2, someText_3, someText_4

-- 1, Joe, 100, 200, 300, null, null, 'value 300', 'value 300', 'value 300', null, null

-- 2, Sara, 100, 150, null, null, null, 'value 100, 'value 150', null, null, null


As you can see, I do not know how many records there are per partition (custId or custName) and what their values are. In the example above, there are 2 partitions, one having 3 and the other having 2 records.

What I know (or at least can provide) is a static limit - in this case 4, so the generated columns "_4" will be empty/null for both partitions. The actual values found will be "filled up"

Sounds crazy huh, maybe someone has an idea :-)

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond
    Answer ✓

    Something like this then...

    SQL> with pvt_test(custId,custName,someValue,someText) as (
      2    select 1, 'Joe', 100, 'value 300' from dual union all
      3    select 1, 'Joe', 200, 'value 300' from dual union all
      4    select 1, 'Joe', 300, 'value 300' from dual union all
      5    select 2, 'Sara', 100, 'value 100' from dual union all
      6    select 2, 'Sara', 150, 'value 150' from dual
      7    )
      8    ,ord as (
      9    select custId, custName, someValue, someText
     10          ,row_number() over (partition by custId,custName order by someValue) as rn
     11    from   pvt_test
     12    )
     13  select *
     14  from   ord
     15         pivot (max(someValue) as someValue,
     16                max(someText) as someText
     17                for rn in (1,2,3,4)
     18               ) p
     19  order by 1
     20  /
    
        CUSTID CUST 1_SOMEVALUE 1_SOMETEX 2_SOMEVALUE 2_SOMETEX 3_SOMEVALUE 3_SOMETEX 4_SOMEVALUE 4_SOMETEX
    ---------- ---- ----------- --------- ----------- --------- ----------- --------- ----------- ---------
             1 Joe          100 value 300         200 value 300         300 value 300
             2 Sara         100 value 100         150 value 150
    


    User_2SRRN

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond

    Not crazy. Because you know there is a static limit to how many values you want, you can just assign a row number to each row within the group and then pivot on that row number...

    old style (as you haven't mentioned version)...

    SQL> with pvt_test(custId,custName,someValue,someText) as (
      2    select 1, 'Joe', 100, 'value 300' from dual union all
      3    select 1, 'Joe', 200, 'value 300' from dual union all
      4    select 1, 'Joe', 300, 'value 300' from dual union all
      5    select 2, 'Sara', 100, 'value 100' from dual union all
      6    select 2, 'Sara', 150, 'value 150' from dual
      7    )
      8    ,ord as (
      9    select custId, custName, someValue, someText
     10          ,row_number() over (partition by custId,custName order by someValue) as rn
     11    from   pvt_test
     12    )
     13  select custId, custName
     14        ,max(case when rn = 1 then someValue else null end) as someValue_1
     15        ,max(case when rn = 2 then someValue else null end) as someValue_2
     16        ,max(case when rn = 3 then someValue else null end) as someValue_3
     17        ,max(case when rn = 4 then someValue else null end) as someValue_4
     18        ,max(case when rn = 1 then someText else null end) as someText_1
     19        ,max(case when rn = 2 then someText else null end) as someText_2
     20        ,max(case when rn = 3 then someText else null end) as someText_3
     21        ,max(case when rn = 4 then someText else null end) as someText_4
     22  from   ord
     23  group by custId, custName
     24  order by 1
     25  /
    
        CUSTID CUST SOMEVALUE_1 SOMEVALUE_2 SOMEVALUE_3 SOMEVALUE_4 SOMETEXT_ SOMETEXT_ SOMETEXT_ SOMETEXT_
    ---------- ---- ----------- ----------- ----------- ----------- --------- --------- --------- ---------
             1 Joe          100         200         300             value 300 value 300 value 300
             2 Sara         100         150                         value 100 value 150
    


    For more recent versions of the database you can take the data once the row number is assigned and use the PIVOT clause instead (you can look that up easily enough - plenty of examples out there)

    User_2SRRN
  • User_2SRRN
    User_2SRRN Member Posts: 12 Green Ribbon

    oh thanks :-)

    It's Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond
    Answer ✓

    Something like this then...

    SQL> with pvt_test(custId,custName,someValue,someText) as (
      2    select 1, 'Joe', 100, 'value 300' from dual union all
      3    select 1, 'Joe', 200, 'value 300' from dual union all
      4    select 1, 'Joe', 300, 'value 300' from dual union all
      5    select 2, 'Sara', 100, 'value 100' from dual union all
      6    select 2, 'Sara', 150, 'value 150' from dual
      7    )
      8    ,ord as (
      9    select custId, custName, someValue, someText
     10          ,row_number() over (partition by custId,custName order by someValue) as rn
     11    from   pvt_test
     12    )
     13  select *
     14  from   ord
     15         pivot (max(someValue) as someValue,
     16                max(someText) as someText
     17                for rn in (1,2,3,4)
     18               ) p
     19  order by 1
     20  /
    
        CUSTID CUST 1_SOMEVALUE 1_SOMETEX 2_SOMEVALUE 2_SOMETEX 3_SOMEVALUE 3_SOMETEX 4_SOMEVALUE 4_SOMETEX
    ---------- ---- ----------- --------- ----------- --------- ----------- --------- ----------- ---------
             1 Joe          100 value 300         200 value 300         300 value 300
             2 Sara         100 value 100         150 value 150
    


    User_2SRRN
  • User_2SRRN
    User_2SRRN Member Posts: 12 Green Ribbon

    thank you - this is it :-)