String aggregate in legacy in sql server

Question

I have this table

grid_id | criteria_id | start_value| end_value | provider | property1 | property2 | property3
--------|-------------|------------|-----------|----------|-----------|-----------|-----------
   1    |    1        |   3        |    NULL   | internal |    1      |    1      |     1   
   2    |    1        |   1        |    NULL   | internal |    1      |    1      |     1   
   3    |    2        |   1        |    NULL   | internal |    1      |    9      |     1   
   4    |    3        |   1        |    100    | internal |    2      |    5      |     1   
   5    |    1        |   2        |    NULL   | external |    1      |    7      |     1

What I would like is to create a select which returns the data like this :

 criteria_id | start_value| end_value | provider | property1 |  property2 | property3
-------------|------------|-----------|----------|-----------|----------- |-----------
   1         |    3,1     |  NULL     | internal |     1     |     1      |     1   
   2         |     1      |  NULL     | internal |     1     |     9      |     1   
   3         |     1      |  100      | internal |     2     |     5      |     1   
   1         |     2      |  NULL     | external |     1     |     7      |     1

In table criteria I have the name of the criterias and the information if is a range or not (for example criteria 3 is a range and I won't need to create a comma separated value in start_value for it):

criteria table:

criteria_id | criteria_name| is_range 
------------|--------------|---------
    1       |     crit_1   |    0   
    2       |     crit_2   |    0   
    3       |     crit_3   |    1

SELECT c.criteria_name AS criteria
   ,CASE WHEN c.is_by_range = 0
      THEN (IsNull(STUFF((
        SELECT ', ' + CAST(g.start_value AS VARCHAR)
        FROM survey_reallocation_scoring_grid g1
        WHERE g.grid_id = g1.grid_id AND g.criteria_id = c.criteria_id
        FOR XML PATH('')), 1, 2, ''), ''))
    ELSE 
        CAST(g.start_value AS VARCHAR)
    END AS start_value
,g.end_value 
,g.provider 
,g.property1 
,g.property2 
,g.property3 
FROM [grid] g
INNER JOIN criteria c ON g.criteria_id = c.criteria_id
GROUP BY g.grid_id, c.criteria_name,c.criteria_reallocation_scoring_id,c.is_range,g.end_value,g.provider,g.property1,g.property2,g.property3
ORDER BY g.grid_id

I want to create comma separated values for start_value where there is a criteria on multiple rows with exactly same properties besides start_value (end_value, provider,property1,property2,property3) and the criteria is not a range. I don't want the value from row 5 even if it's still criteria 1 because it has different properties from the ones on row 1 and 2 (in the initial table). I tried with STUFF and GROUP BY and WITH CTE but because I need to keep the order from the initial table I don't manage to achieve the desired results. The other questions on the topic are a bit easier than this case and I ran out of ideas, I hope someone will have some hints..Thank you !

PS: I cannot use STRING_AGG because we have below 2017 SQL Server version. :(

*"I tried with STUFF and GROUP BY and WITH CTE but because I need to keep the order from the initial table I don't manage to achieve the desired results. "* Show us that attempt in your question. If you need data in a certain order, then use an `ORDER BY`. — Thom A, Feb 02 '21 at 12:44
Also, if you're on a recent version of SQL Server, you will have access to `STRING_AGG`. — Thom A, Feb 02 '21 at 12:54
I cannot use STRING_AGG because we have below 2017 Sql Server version. :( — White_Noise, Feb 02 '21 at 13:53
I updated my question, that query fails because I don't have start_value in the GROUP BY, but if I add it I won't have the desired filter, because I don't need to group by start_value.. — White_Noise, Feb 02 '21 at 14:32

Slava Rozhnev · Accepted Answer · 2021-02-02T15:55:36.787

Is below query answer to your question:

select 
    criteria_id, STRING_AGG(start_value, ',') start_value, end_value, provider, property1, property2, property3
from Tbl
group by criteria_id, end_value, provider, property1, property2, property3
order by min(grid_id)
;

Share SQL code

Result:

+=============+=============+===========+==========+===========+===========+===========+
| criteria_id | start_value | end_value | provider | property1 | property2 | property3 |
+=============+=============+===========+==========+===========+===========+===========+
| 1           | 3,1         | (null)    | internal | 1         | 1         | 1         |
+-------------+-------------+-----------+----------+-----------+-----------+-----------+
| 2           | 1           | (null)    | internal | 1         | 9         | 1         |
+-------------+-------------+-----------+----------+-----------+-----------+-----------+
| 3           | 1           | 100       | internal | 2         | 5         | 1         |
+-------------+-------------+-----------+----------+-----------+-----------+-----------+
| 1           | 2           | (null)    | external | 1         | 7         | 1         |
+-------------+-------------+-----------+----------+-----------+-----------+-----------+

For older MS SQL server you can use next solution:

select 
    criteria_id, 
    STUFF((
        SELECT ',' + CAST(t.start_value as varchar(10))
        FROM Tbl AS t
        WHERE
            Tbl.criteria_id = t.criteria_id
            and (Tbl.end_value = t.end_value or (Tbl.end_value is null and t.end_value is null))
            and Tbl.provider = t.provider
            and Tbl.property1 = t.property1
            and Tbl.property2 = t.property2
            and Tbl.property3 = t.property3
        FOR XML PATH('')
    ), 1, 1, '') start_value,
    end_value, provider, property1, property2, property3
from Tbl
group by criteria_id, end_value, provider, property1, property2, property3
order by min(grid_id)
;

Share SQL code

Unfortunately I cannot use STRING_AGG because we have below 2017 Sql Server version. :( — White_Noise, Feb 02 '21 at 13:52

String aggregate in legacy in sql server

1 Answers1