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. :(
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)
;
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)
;