Extract Delimited values and create columns with T-SQL

Question

I have the following table called DQS_Raw with two columns.

DQS_Raw

id    Result
-----------------    
1   |01|00|00|01
2   |00|01|01|00
3   |00|00|00|01

I need to use a stored procedure to get the below table result.

The below table has 6 columns.

DQS_Raw

id  Result        VF02a VF02b VF02c VF05b
------------------------------------------    
1   |01|00|00|01  01    00    00    01
2   |00|01|01|00  00    01    01    00
3   |00|00|00|01  00    00    00    01

As noted here the values between the pipe delimited characters are extracted. The first extracted values are assigned to the column VF02a, the second assigned to VF02b and so on.

I also need this to be dynamic enough in case the order changes from the input.

Please don't use a set based approach.

Jono

@a_horse_with_no_name I guess that's what he's doing by splitting it up? :) — Joachim Isaksson, Aug 01 '13 at 09:08
I wish I could but business has decided otherwise...My process only starts from picking it up with delimited values... — Jonathan Cubitt, Aug 01 '13 at 09:10
@JoachimIsaksson: well not really: he is trying to retrieve the comma separated values in a "dynamic" manner. Doesn't sound like he actually wants to fix the data model. — , Aug 01 '13 at 09:11
SQL is all about using "Set based approaches". If you don't want to, perhaps you should use a different language. — podiluska, Aug 01 '13 at 09:13
Similar Question is [Here](http://stackoverflow.com/questions/17968607/calculating-the-number-of-hosts-in-a-netmask-using-tsql/17969556#17969556) — Romesh, Aug 01 '13 at 09:13

score 1 · Accepted Answer · edited Aug 01 '13 at 09:54

1

I think it would be impossible to make this a dynamic approach.

But you can do something like this to split the value

I assume your using ssms 2010 server

select *,  
SUBSTRING(Result,2,2) as VF021,  
SUBSTRING(Result,5,2) as VF02b,  
SUBSTRING(Result,8,2) as VF02c,  
SUBSTRING(Result,11,2) as VF05b  
from DQS_RAW

edited Aug 01 '13 at 09:54

Prahalad Gaggar

11,389
16
53
71

answered Aug 01 '13 at 09:14

zxc

1,504
3
13
32

@JonathanCubitt Tnx! :) – zxc Aug 01 '13 at 09:33

Extract Delimited values and create columns with T-SQL

1 Answers1