CODEWITHSUNDEEP
sqloracle-databaseplsql
Mustafa
Mustafa

Reputation: 63

How to Split a row into multiple columns from multi tables

I have 2 table and They in a relationships:

select * from tableA,tableB
inner join tableA.id=tableB.id

and this query give me:

tableACol1  tableACol2 tableACol3  tableBCol1  tableBCol2 tableBCol3
a;b;c       1;2;3      t           x;y         8;9        p

and also i want do this :

col1 col2 col3 col3  col4  col5 col6  col6  col7  col8  col9 col10
a    1    b    2     c     3    t     x     8     y     9    p

My columns are dynamic.

How can i do this? Thanks.

Upvotes: 0

Views: 46

Answers (1)

MT0
MT0

Reputation: 167982

Assuming that you always have at most 3 items for table A and 2 items for table B then you can use:

SELECT REGEXP_SUBSTR(a1, '[^;]+', 1, 1) AS col1,
       REGEXP_SUBSTR(a2, '[^;]+', 1, 1) AS col2,
       REGEXP_SUBSTR(a1, '[^;]+', 1, 2) AS col3,
       REGEXP_SUBSTR(a2, '[^;]+', 1, 2) AS col3,
       REGEXP_SUBSTR(a1, '[^;]+', 1, 3) AS col4,
       REGEXP_SUBSTR(a2, '[^;]+', 1, 3) AS col5,
       A3 AS col6,
       REGEXP_SUBSTR(b1, '[^;]+', 1, 1) AS col6,
       REGEXP_SUBSTR(b2, '[^;]+', 1, 1) AS col7,
       REGEXP_SUBSTR(b1, '[^;]+', 1, 2) AS col8,
       REGEXP_SUBSTR(b2, '[^;]+', 1, 2) AS col9,
       B3 AS col10
FROM   your_query;

Your column naming has been replicated in the output but it is better practice to name columns uniquely.

Which, for the sample, data:

CREATE TABLE your_query (A1, A2, A3, B1, B2, B3) AS
SELECT 'a;b;c', '1;2;3', 't', 'x;y', '8;9', 'p' FROM DUAL

Outputs:

COL1 COL2 COL3 COL3 COL4 COL5 COL6 COL6 COL7 COL8 COL9 COL10
a 1 b 2 c 3 t x 8 y 9 p

db<>fiddle here

If you have a variable number of columns then you would be best performing the transformation in whatever middle-tier language (i.e. C#, PHP, Java, C++) you are using to access the database as SQL (not just Oracle SQL) must have a fixed number of known columns.

It is possible to use dynamic SQL within PL/SQL to programmatically generate an SQL statement and then use EXECUTE IMMEDIATE but I would not recommend it as it would be complicated and is likely to be much slower than performing the transformation in the middle tier.

Upvotes: 1

Related Questions