Hello,
Assumen I have this table_x, which as PI names listed for each Site.
CREATE TABLE table_x
( First_name VARCHAR2 (40),
Last_name VARCHAR2 (40),
Site_ID
);
INSERT INTO table_x (addr) VALUES ('A', 'B', 101);
INSERT INTO table_x (addr) VALUES ('C', 'B', 101);
INSERT INTO table_x (addr) VALUES ('D', 'E', 101);
INSERT INTO table_x (addr) VALUES ('G', 'B', 102);
INSERT INTO table_x (addr) VALUES ('A', 'R', 102);
INSERT INTO table_x (addr) VALUES ('AA', 'B', 102);
INSERT INTO table_x (addr) VALUES ('CC', 'B', 102);
INSERT INTO table_x (addr) VALUES ('D', 'EE', 102);
INSERT INTO table_x (addr) VALUES ('GF', 'B', 102);
INSERT INTO table_x (addr) VALUES ('A', 'RG', 102);
I can use listagg to concatenate all the PI names into one string for each site
and then list in this way:
Site_ID PI
101 A B; C B; D E;
102 G B; A R; AA B; CC B; D EE; GF B; A RG
But I wonder if I can use pivot to list each row of PI name as a single column
and the issue is the PI name rows for each site is dynamic.
Site_ID PI_1 PI_2 PI_3 PI_4 PI_5 PI_6 PI_7
101 A B C B D E
102 G B A R AA B CC B D EE GF B A RG
Another question is if it's possible to split the string (delimited by ; a
nd the substr count is dynamic) to multiple columns? Say, convert from result 1 to result 2.
Thanks.