on 2016 Nov 30 11:30 AM
I have tre columns i a SQL with names Material, pos, and value. Pos is a comma seperatde and value also the same.
Based column pos and column value, I want to create a field in my SQL. Pos contain comma separated values indicating from which pos column value should have. The new column will contain 18 numerical values.
Ex:
Material-----Pos------Value
Test123------1,10 ----002,001
I need a third column that should look like this: 002000000001000000
Value 002 starting on pos 1. It is filled with zeros to the value of 001 that starts at pos 10. Then it is filled with zeros until it becomes 18 characters.
Pos and value have always just as many kommasepaerade värdern and it can sometimes be more or less values.
Ex:
Material-----Pos------Value
Test123------1,10,15--002,001,003
It should look like this: 002000000001000030
How do I do this in the best way? I guess I should use function INSERTSTR or STUFF on some way...
Request clarification before answering.
The sa_split_list() procedure and STUFF function may help; here is a demonstration...
BEGIN DECLARE @pos LONG VARCHAR; DECLARE @value LONG VARCHAR; DECLARE @result VARCHAR ( 18 ); SET @pos = '1,10'; SET @value = '002,001'; SET @result = REPEAT ( '0', 18 ); FOR f_fetch1 AS c_fetch1 INSENSITIVE CURSOR FOR SELECT pos_entry.line_num AS @entry_number, pos_entry.row_value AS @entry_pos, value_entry.row_value AS @entry_value FROM ( SELECT line_num, row_value FROM sa_split_list ( @pos ) ) AS pos_entry INNER JOIN ( SELECT line_num, row_value FROM sa_split_list ( @value ) ) AS value_entry ON pos_entry.line_num = value_entry.line_num ORDER BY @entry_number FOR READ ONLY DO SET @result = STUFF ( @result, @entry_pos, LENGTH ( @entry_value ), @entry_value ); END FOR; SELECT @result; SET @pos = '1,10,15'; SET @value = '002,001,003'; SET @result = REPEAT ( '0', 18 ); FOR f_fetch2 AS c_fetch2 INSENSITIVE CURSOR FOR SELECT pos_entry.line_num AS @entry_number, pos_entry.row_value AS @entry_pos, value_entry.row_value AS @entry_value FROM ( SELECT line_num, row_value FROM sa_split_list ( @pos ) ) AS pos_entry INNER JOIN ( SELECT line_num, row_value FROM sa_split_list ( @value ) ) AS value_entry ON pos_entry.line_num = value_entry.line_num ORDER BY @entry_number FOR READ ONLY DO SET @result = STUFF ( @result, @entry_pos, LENGTH ( @entry_value ), @entry_value ); END FOR; SELECT @result; END; @result ------------------ 002000000001000000 @result ------------------ 002000000001000030
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here's a variation of Breck's solution that does not require a loop - instead it uses
-- sample with Breck's second test data BEGIN DECLARE @pos LONG VARCHAR; DECLARE @value LONG VARCHAR; DECLARE @result VARCHAR ( 18 ); SET @pos = '1,10,15'; SET @value = '002,001,003'; SET @result = ''; SELECT LIST( @padded_entry , '' ) INTO @RESULT FROM (SELECT DT1.*, -- calculate the position of the next entry (or MAX LENGTH + 1 for the last entry) ISNULL( FIRST_VALUE( @entry_pos ) OVER ( ORDER BY @entry_number ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ), 19 ) AS @next_entry_pos, -- calculate the length of the padding needed for the current entry @next_entry_pos - @entry_pos - LENGTH( @entry_value ) AS @entry_padding_length, @entry_value || REPEAT( '0', @entry_padding_length ) AS @padded_entry FROM (SELECT pos_entry.line_num AS @entry_number, CAST(pos_entry.row_value AS INT) AS @entry_pos, value_entry.row_value AS @entry_value FROM ( SELECT line_num, row_value FROM sa_split_list ( @pos ) ) AS pos_entry INNER JOIN ( SELECT line_num, row_value FROM sa_split_list ( @value ) ) AS value_entry ON pos_entry.line_num = value_entry.line_num ORDER BY @entry_number) DT1 ) DT2 ORDER BY 1; SELECT @result; END;
returns
'002000000001000030'
FWIW, here's the result of the derived table DT2 (i.e. before the LIST aggregate is used):
@entry_number,@entry_pos,@entry_value,@next_entry_pos,@entry_padding_length,@padded_entry 1,1,002,10,6,002000000 2,10,001,15,2,00100 3,15,003,19,1,0030
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.