Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dynamic pivot: generate static columns from uknown records

User_2SRRNJun 9 2022

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 :-)

This post has been answered by BluShadow on Jun 9 2022
Jump to Answer

Comments

Post Details

Added on Jun 9 2022
4 comments
288 views