comma separated string to multiple rows

[ahad80pk]

Hi

I have a VDB connection for oracle database. One of the source tables contains comma separated values which I want to split in individual rows.

name	value
A	0.0,413.0,421.0,431.0,441.0,451.0,461.0,471.0,481.0,491.0,501.0,506.0,511.0,521.0
B	671.0,681.0,691.0,701.0,711.0,721.0,731.0,741.0,751.0,761.0,771.0,781.0,791.0,801.0,811.0,821.0,846.0

name	value
A	0.0
A	413.0
A	421.0
....	.....

How to write a VDB view to my required result?

[rareddy]

Use TextTable but define the new row delimiter as comma (,) in its use. Read about texttable here TEXTTABLE · Teiid Documentation

[ahad80pk]

Thanks Ramesh for helping me but I am stuck at one point may be you can guide me.

This is how my data looks like in Oracle table

well name	survey name	npts	depth	azimuth	inclination
Well-01	survey_01	6	10,20,30,40,50,60	0,8.05,8.05,10.73,5.80,10.46	0.00,0.96,1.10,1.20,1.47,1.56

And this is what I want to achieve

well name	survey name	npts	depth	azimuth	inclination
Well-01	survey_01	6	10	0	0
Well-01	survey_01	6	20	8.05	0.96
Well-01	survey_01	6	30	8.05	1.10
Well-01	survey_01	6	40	10.73	1.20
Well-01	survey_01	6	50	5.80	1.47
Well-01	survey_01	6	60	10.46	1.56

I am able to use texttable on one csv value column as nested table but how can I build relationship between other two csv columns using texttables maintaining the data integrity?

[rareddy]

A SQL like below *should* work

SELECT MyOrclTbl.wellname, MyOrclTbl.surveyname, MyOrclTbl.npts, depthTable.depth, azimuthTable.azimuth,inclinationTable.inclination
FROM 
(select 'Well-01' as wellname, 
   'survey_01' as surveyname, 
   6 as npts, 
   '10,20,30,40,50,60' depth, 
   '0,8.05,8.05,10.73,5.80,10.46' azimuth, 
   '0.00,0.96,1.10,1.20,1.47,1.56' as inclination
) as MyOrclTbl, 
TEXTTABLE (MyOrclTbl.depth COLUMNS depth integer, id for ordinality ROW DELIMITER ',' DELIMITER '-') as depthTable
LEFT JOIN TEXTTABLE (MyOrclTbl.azimuth COLUMNS azimuth double, id for ordinality ROW DELIMITER ',' DELIMITER '-') as azimuthTable ON depthTable.id = azimuthTable.id
LEFT JOIN TEXTTABLE (MyOrclTbl.inclination COLUMNS inclination double, id for ordinality ROW DELIMITER ',' DELIMITER '-') as inclinationTable ON depthTable.id = inclinationTable.id

Where you would replace the "MyOrclTbl" with your actual table reference. I tested with Teiid 10.2 this brings back results as you expected.

HTH

Ramesh..

[ahad80pk]

Thanks Ramesh. After updating teiid, I am able to bring the required result. Thanks for you support as always.