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

asked 14 Mar, 06:08

mfkpie8's gravatar image

mfkpie8
184475564
accept rate: 14%

edited 14 Mar, 07:34

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.

(14 Mar, 10:20) Breck Carter

Can I give you an example of this? Thank you very much

(14 Mar, 23:46) mfkpie8

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:

  • I used a CROSS APPLY operator to provide the sa_split_list procedure with the LOT_NO value for each row of your table and added the ZT.LOT_NO column to the inner query in order to pretain the original order.
  • The pivot condition 'in (1 as "column1", 2 as ...' is handy to provide aliases for the pivoted columns, by default they would be named "1_column", "2_column" and so on.

(*): 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.

permanent link

answered 15 Mar, 13:16

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

edited 15 Mar, 15:59

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16

question asked: 14 Mar, 06:08

question was seen: 175 times

last updated: 15 Mar, 15:59