John Pietrar John Pietrar - 2 months ago 14
SQL Question

patterns in sql

if you have something like :

text
result
measure
text
measure
result
text
result
text
measure
text
measure
result
text
measure
text


how could you extract each measure and result between 2 texts ,ok i extracted the results and measures that I want and stored them in variables but what can i do where i have :

text
measure
text


and I would like to extract the result as NULL in this case....

Answer

If you're pulling data in from a text file, then your best option is probably to set up the table you're importing to with an identity field so that as you load it, the values are assigned a number in order:

DECLARE @table TABLE
        (RowNum INT IDENTITY
        ,val VARCHAR(36));

INSERT  INTO @table
VALUES  ('text'),
        ('result'),
        ('measure'),
        ('text'),
        ('measure'),
        ('result'),
        ('text'),
        ('result'),
        ('text'),
        ('measure'),
        ('text'),
        ('measure'),
        ('result'),
        ('text'),
        ('measure'),
        ('text');

The resulting table will then look like this:

RowNum  val
1   text
2   result
3   measure
4   text
5   measure
6   result
7   text
8   result
9   text
10  measure
11  text
12  measure
13  result
14  text
15  measure
16  text

From there we can go about finding the results and measures between the text.

SELECT  Results.Result
       ,Measures.Measure
FROM    ((
          SELECT RowNum AS Start FROM @table WHERE val = 'text'
         ) AS A
         OUTER APPLY (
                      SELECT TOP 1 RowNum AS [End] 
                      FROM      @table endTab 
                      WHERE     val = 'text' 
                                AND A.Start < endTab.RowNum
                      ORDER BY RowNum
                     ) AS B
         OUTER APPLY (
                      SELECT TOP 1
                                val AS Result
                      FROM      @table resultTab
                      WHERE     val = 'result'
                                AND resultTab.RowNum BETWEEN A.Start AND B.[End]
                     ) AS Results
         OUTER APPLY (
                      SELECT TOP 1
                                val AS Measure
                      FROM      @table measureTab
                      WHERE     val = 'measure'
                                AND measureTab.RowNum BETWEEN A.Start AND B.[End]
                     ) AS Measures);

First, we need to get the positions of each text record and its following text record. Then we can look for the first Result record and Measure record within the start and end.

Result  Measure
result  measure
result  measure
result  NULL
NULL    measure
result  measure
NULL    measure
Comments