|
To generate a dynamic column with; split a column of data CREATE TABLE "DBA"."ztest" (
"LOT_NO" CHAR(331) NOT NULL,
PRIMARY KEY ( "LOT_NO" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."ztest" IS '
';
INSERT INTO "DBA"."ztest" ("LOT_NO") VALUES('NO001;A01;B20190301001');
INSERT INTO "DBA"."ztest" ("LOT_NO") VALUES('N0002;A02;C20190503001;D20255');
INSERT INTO "DBA"."ztest" ("LOT_NO") VALUES('NO003;A03;B201903001;C205000');
Is there any function that can generate multiple columns dynamically,thanks column1 column2 column3 column4 NO001 A01 B20190301001 N0002 A02 C20190503001 D20255 NO003 A03 B201903001 C205000 |
|
If there are at most 4 different values (*) in the LOT_NO column and you are using v17, you can use the PIVOT operator to build such a query. As Breck has stated, the sa_split_list() system procedure is handy to separate values from a single value/column into separate rows. In contrast, the PIVOT operator is used to turn "rows into columns". The following query returns exactly your sample result. select PT.column1, PT.column2, PT.column3, PT.column4
from
(select ZT.LOT_NO, line_num, row_value
from ztest ZT
cross apply sa_split_list(ZT.LOT_NO, ';') SL
order by ZT.LOT_NO, SL.line_num) ST
pivot (min(ST.row_value)
for ST.line_num in (1 as "column1", 2 as "column2", 3 as "column3", 4 as "column4")) PT
order by LOT_NO;
Two further remarks:
(*): Note, the sample query just assumes 4 columns, of course you could either enhance that to a higher maximum value, or if the number should not be delimited, use a sub query for the IN list - but in the latter case you cannot specify aliases, AFAIK. |
Perhaps start with sa_split_list() to build a string containing an INSERT other_table, then run it with EXECUTE IMMEDIATE, inside a FOR loop to step through the rows in ztest.
Can I give you an example of this? Thank you very much