Forum Stats

  • 3,839,773 Users
  • 2,262,535 Discussions
  • 7,901,053 Comments

Discussions

Categories

dynamic pivot: generate static columns from uknown records

User_2SRRN
User_2SRRN Member Posts: 12 Green Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • User_2SRRN
    User_2SRRN Member Posts: 12 Green Ribbon

    thank you - this is it :-)

    · Share on TwitterShare on Facebook