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