CODEWITHSUNDEEP
sqlstringcsvstored-proceduresdb2
Mahmoud
Mahmoud

Reputation: 883

how to convert multiple comma separated strings into a table with several columns

I have a table with two columns (ID, Asset) in the database. Now I need to pass a list of tuples (ID1, ID2, QTY) to a stored procedure and decrease assets of ID2 by QTY and add it to ID1. I came up with passing these values as three comma-separated strings with the same length in the number of elements in each string. Then I want to parse these strings and convert them to a temp table and finally do the required calculations and updates. For example, with the following inputs ID1: 101,231,452,176 ID2: 301,453,54,1265 QTY: 1000,2000,7412,6521 the result table should be as following:


ID1 |   ID2  |  QTY
--------------------
101 |   301  |  1000
231 |   453  |  2000
452 |   54   |  7412
176 |   1265 |  6521

Upvotes: 0

Views: 106

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

You may use a "tokenizer" function like below:

select 
  id1.seq
, id1.tok as id1
, id2.tok as id2
, qty.tok as qty
from xmltable('for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
passing '101,231,452,176' as "s"
columns 
  seq for ordinality
, tok int path '.'
) id1
join xmltable('for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
passing '301,453,54,1265' as "s"
columns 
  seq for ordinality
, tok int path '.'
) id2 on id2.seq = id1.seq 
join xmltable('for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
passing '1000,2000,7412,6521' as "s"
columns 
  seq for ordinality
, tok int path '.'
) qty on qty.seq = id1.seq;

The result is:

|SEQ                 |ID1        |ID2        |QTY        |
|--------------------|-----------|-----------|-----------|
|1                   |101        |301        |1000       |
|2                   |231        |453        |2000       |
|3                   |452        |54         |7412       |
|4                   |176        |1265       |6521       |

Parameter can be used instead of a string constant after passing clause inside each of xmltable.

Upvotes: 2

Related Questions