Query to derive date and rate from an input string-CodePudding

In SQL server I need to derive dates and rates from given input. Date will come after 5; and rate will come after 3; There can be multiple records of date and rate can be available in the same input (concatenate those with comma in the result).

It has 2 rows. First one with single date and rate. Second one with multiple date and rates. There can be n number of dates and rates possible within the input string. Please suggest and a simple SQL query to derive the same. I am using Microsoft SQL server 2012 only, so latest functions came after that I can't use.

Input (2 rows):

;2;1;2;5;20270608;3;100.000000;
;2;203;2;5;19680515;3;100.000000;5;19690515;3;100.000000;5;19700515;3;100.000000;5;19710515;3;100.000000;5;19720515;3;100.000000;

Output date (2 rows):

20270608
19680515,19690515,19700515,19710515,19720515

Output rate (2 rows):

100.000000
100.000000,100.000000,100.000000,100.000000,100.000000

CodePudding user response：

As @JamesZ already said in the comments, you can do this with the lead function

declare @test varchar(1000) = ';2;203;2;5;19680515;3;100.000000;5;19690515;3;100.000000;5;19700515;3;100.000000;5;19710515;3;100.000000;5;19720515;3;100.000000;'

select string_agg(t2.nextvalue, ',')
from   ( select t.value,
                lead(value, 1) over (order by one) nextvalue
         from   ( select '1' as one,
                         value
                  from   STRING_SPLIT(@test ,';')
                  where value is not null
                  and   value <> ''
                ) t
      ) t2
where t2.value = '5'

the result is

19680515,19690515,19700515,19710515,19720515

To get the rates just do

where t2.value = '3'

Try it yourself in this DBFiddle

EDIT

Since sql server 2012 has no split_string function you can use the DelimitedSplit8K function instead

And since it also does not have the string_agg function we will have to use xml instead

And then we use stuff to get rid of the extra , at the start

select stuff ( ( select ','   t2.nextvalue
                 from   ( select t.item,
                                 lead(item, 1) over (order by one) nextvalue
                          from   ( select '1' as one,
                                          item
                                   from   DelimitedSplit8K(@test, ';')
                                   where item is not null
                                   and   item <> ''
                                 ) t
                        ) t2
                where t2.item = '5'
                for XML PATH('')
               ), 
               1, 1, ''
             ) as dates

new DBFiddle

CodePudding user response：

Thanks for all your responses. I had slightly changed the response from @GuidoG and written the query without using function as below,

SELECT 
  string_agg(t2.nextvalue, ',') 
FROM 
  (
    SELECT 
      t.item, 
      Lead(item, 1) OVER (
        ORDER BY 
          one
      ) nextvalue 
    from 
      (
        SELECT 
          '1' AS one, 
          item 
        from 
          (
            SELECT 
              split.a.value('.', 'VARCHAR(100)') AS item 
            from 
              (
                SELECT 
                  cast (
                    '<m>'   replace(@test, ';', '</m><m>')   '</m>' as xml
                  ) as data
              ) AS x cross apply data.nodes ('/M') AS split(a)
          ) AS i 
        where 
          item IS NOT null 
          and item <> ''
      ) t
  ) t2 
where 
  t2.item = '5'