SQL Server Splits into table

Question

I have four strings with delimiter ‘|’

accounts = '1|5|12|18'
debit = '100|50|0|0'
credit = '0|0|20|130'
branches = '121|569|901|1002'

And have to insert into table columns as below

+----------+-------+--------+----------+
| accounts | debit | credit | branches |
+----------+-------+--------+----------+
|    1     |  100  |   0    |   121    |
|    5     |  50   |   0    |   569    |
|    12    |  0    |   20   |   901    |
|    18    |  0    |   130  |   1002   |
+----------+-------+--------+----------+

Please help Thanks

you will need one string split function that will return table. refer this, http://stackoverflow.com/questions/2647/split-string-in-sql — KumarHarsh, Dec 19 '13 at 02:10
I have defined the function, but returns every possible combination (256) and I want to return only those 4 records. The link you provided me only use a string I do not know how to combine all of them — Rodrigo, Dec 19 '13 at 02:28

score 0 · Accepted Answer · answered Dec 19 '13 at 02:52

see from that link use dbo.fn_Split then,

Declare  @accounts varchar(200) = '1|5|12|18'
Declare @debit varchar(200)= '100|50|0|0'
Declare @credit varchar(200)= '0|0|20|130'
Declare @branches varchar(200)= '121|569|901|1002'
Declare @delim char(1)='|'

insert into table1
select a.value as accounts,b.value as debit,c.value as credit,d.value as branch from dbo.fn_Split(@accounts,@delim,null) a
left join dbo.fn_Split(@debit,@delim,null) b on a.id=b.id
left join dbo.fn_Split(@credit,@delim,null) c on a.id=c.id
left join dbo.fn_Split(@branches,@delim,null) d on a.id=d.id

SQL Server Splits into table

1 Answers1