I have the following database model:
criteria table:
criteria_id criteria_name is_range
1 product_category 0
2 product_subcategory 0
3 items 1
4 criteria_4 1
evaluation_grid table:
evaluation_grid_id criteria_id start_value end_value provider property_1 property_2 property_3
1 1 3 NULL internal 1 1 1
2 1 1 NULL internal 1 1 1
3 2 1 NULL internal 1 2 1
4 3 1 100 internal 2 1 1
5 4 1 50 internal 2 2 1
6 1 2 NULL external 2 8 1
7 2 2 NULL external 2 5 1
8 3 1 150 external 2 2 2
9 3 1 100 external 2 3 1
product_category table:
id name
1 test1
2 test2
3 test3
product_subcategory table:
id name
1 producttest1
2 producttest2
3 producttest3
What I am trying to achieve is returning the values like this:
criteria start_value end_value provider property_1 property_2 property_3
product_category test3, test1 NULL internal 1 1 1
product_subcategory producttest1 NULL internal 1 2 1
items 1 100 internal 2 1 1
criteria_4 1 50 internal 2 2 1
product_category test2 NULL external 2 8 1
product_subcategory producttest2 NULL external 2 5 1
items 1 150 external 2 2 2
criteria_4 1 100 external 2 3 1
Basically keeping the order from table evaluation_grid but grouping only the criterias which are not ranges in comma separated value strings based on start_value, end_value, provier, property_1, property_2 and property_3
I tried like this:
SELECT c.criteria_name AS criteria
,CASE WHEN c.criteria_id = 1
THEN
(IsNull(STUFF((SELECT ', ' + RTRIM(LTRIM(pc.name))
FROM product_category pc
INNER JOIN [evaluation_grid] eg ON eg.start_value=pc.id
WHERE srsg.criteria_id=c.criteria_id
FOR XML PATH('')), 1, 2, ''), ''))
WHEN c.criteria_id = 2
THEN (IsNull(STUFF((SELECT ' , ' + RTRIM(LTRIM(psc.name))
FROM product_subcategory psc
INNER JOIN [evaluation_grid] eg ON eg.start_value=psc.id
WHERE srsg.criteria_id=c.criteria_id
FOR XML PATH('')
), 1, 3, ''), ''))
ELSE
CAST(eg.start_value AS VARCHAR)
END AS start_value
,eg.end_value AS end_value
,eg.provider AS provider
,eg.property_1 AS property_1
,eg.property_2 AS property_2
,eg.property_3 AS property_3
FROM [evaluation_grid] eg
INNER JOIN criteria c ON eg.criteria_id = crs.criteria_id
GROUP BY c.criteria_name,c.criteria_id,c.is_range,eg.start_value,eg.end_value,eg.provider,eg.property_1,eg.property_2,eg.property_3
But it is returning wrong data, like this:
criteria start_value end_value provider property_1 property_2 property_3
product_category test3, test1, test2 NULL internal 1 1 1
product_category test3, test1, test2 NULL external 2 8 1
product_category test3, test1, test2 NULL internal 1 1 1
product_subcategory producttest1,producttest2 NULL internal 1 2 1
product_subcategory producttest1,producttest2 NULL external 2 5 1
items 1 100 internal 1 1 1
items 1 150 external 2 2 2
criteria_4 1 50 internal 2 2 1
criteria_4 1 100 external 2 3 1
I tried some versions with "with cte;" as well but didn't manage to find the solution yet and yes, I checked the similar questions already. :) PS: I cannot use STRING_AGG because we have below 2017 Sql Server version. Any suggestion will be highly appreciated, thanks !
As far as I can tell this query returns the exact output you're looking for.
with cte as (
select c.criteria_name,
eg.evaluation_grid_id,
case when c.criteria_id = 1 then pc.[name]
when c.criteria_id = 2 then psc.[name]
else null end pc_cat,
c.criteria_id,c.is_range, eg.start_value, eg.end_value,
eg.[provider], eg.property_1, eg.property_2,eg.property_3
from @evaluation_grid eg
join @criteria c ON eg.criteria_id = c.criteria_id
left join @product_category pc on eg.start_value=pc.id
left join @product_subcategory psc on eg.start_value=psc.id)
select c.criteria_name as criteria,
case when c.is_range=0 then
STUFF((SELECT ', ' + RTRIM(LTRIM(c2.pc_cat))
FROM cte c2
WHERE c2.criteria_id=c.criteria_id
and c2.is_range=c.is_range
and c2.[provider]=c.[provider]
and c2.property_1=c.property_1
and c2.property_2=c.property_2
and c2.property_3=c.property_3
FOR XML PATH('')), 1, 2, '')
else max(cast(c.start_value as varchar(50))) end as start_value,
c.end_value, c.[provider], c.property_1, c.property_2, c.property_3
from cte c
group by c.criteria_name, c.criteria_id, c.is_range, c.end_value,
c.[provider], c.property_1, c.property_2, c.property_3
order by max(c.evaluation_grid_id);
Output
criteria start_value end_value provider property_1 property_2 property_3
product_category test3, test1 NULL internal 1 1 1
product_subcategory producttest1 NULL internal 1 2 1
items 1 100 internal 2 1 1
criteria_4 1 50 internal 2 2 1
product_category test2 NULL external 2 8 1
product_subcategory producttest2 NULL external 2 5 1
items 1 150 external 2 2 2
criteria_4 1 100 external 2 3 1