I'm trying to concatenate the values Bs of a column depending on the value A of another column all this while counting the ocurrences of said value A, finally the selection of value A depends on the result of a CASE clause of column C and D

If i have this table

I want to select only those of COLUMN A where COLUMN C values are greater than COLUMN D, count the times it happens and finally concatenate COLUMN B values (if apply), something like this :

I've managed to acomplish almost all of this with the next query

SELECT 
    CASE WHEN columnc > columnd THEN [columna] ELSE 'Doesnt apply' END as ResultA,
    STUFF((
        SELECT ','   columnb as 'data()' 
        FROM sometable 
        WHERE (columna = sometable.columna)
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS ResultB,
    COUNT(*) AS count
FROM sometable 
GROUP BY 
    CASE WHEN columnc > columnd THEN [columna] ELSE 'Doesnt apply' END
HAVING COUNT(*) >= 1

But it bring all the COLUMN B values even those where COLUMN A value is not present.

CodePudding user response：

This needs a simple where clause and group by:

select column_a
     , string_agg(column_b, ', ') as listof_b
     , count(*) as match_count
from t
where column_c > column_d
group by column_a

Note that in your original attempt, the for xml subquery needs and column_c > column_d in where clause.