Create comma separated value strings using data from different tables in SQL Server

Question

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 !

Answer 1

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

Answer 2

It's a bit difficult to follow the requirements. Can you review the setup & results below and let us know what the desired resultset should be?

declare @criteria table (criteria_id int, criteria_name varchar(50), is_range bit)
insert into @criteria
    values(1, 'product_category', 0), (2, 'product_subcategory', 0), (3, 'items', 1), (4, 'criteria_4', 1);

declare @evaluation_grid table (evaluation_grid_id int,  criteria_id int, start_value int,  end_value int,  [provider] varchar(50),    property_1 int,    property_2 int, property_3  int);
insert into @evaluation_grid
    values
    (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,                       4,       1,           100,     'external',        2,             3,             1)

declare @product_category table (id int, [name] varchar(50))
insert into @product_category
    values (1, 'test1'), (2, 'test2'), (3, 'test3'), (4, 'test4');

declare @product_subcategory table (id int, [name] varchar(50))
insert into @product_subcategory
    values (1, 'producttest1'), (2, 'producttest2'), (3, 'producttest3');   


select  c.criteria_name, 
        stuff(( select ',' + ipc.[name] 
                from @evaluation_grid ieg 
                join @product_category ipc on ieg.start_value = ipc.id 
                where [provider] = eg.[provider] and property_1 = eg.property_1 and property_2 = eg.property_2 and property_3 = eg.property_3
                order by ieg.evaluation_grid_id 
                for xml path('')), 1 ,1, '') as start_value,
        end_value, 
        [provider], 
        property_1, 
        property_2, 
        property_3
from    @evaluation_grid eg
join    @criteria c on eg.criteria_id = c.criteria_id
where   c.is_range = 0
group
by      c.criteria_name, end_value, [provider], property_1, property_2, property_3
union all
select  c.criteria_name, cast(start_value as varchar(10)), end_value, [provider], property_1, property_2, property_3
from    @evaluation_grid eg
join    @criteria c on eg.criteria_id = c.criteria_id
where   c.is_range = 1;