[Solved] Query SQL Server to get count data with populate string id another table

by


As a general disclaimer, you should usually avoid storing CSV data in your tables because it is the epitomy of denormalization. Instead, store each CSV value on a separate row.

To add to this problem, SQL Server has no built in function like MySQL’s FIND_IN_SET() which can conveniently search for a string inside a CSV value. Instead, we have to resort to using several LIKE comparisons to assert if a value be contained inside CSV string.

SELECT
    m1.IdReferensi,
    m1.Referensi,
    m2.Total
FROM Master m1
INNER JOIN
(
    SELECT
        t1.IdReferensi,
        COUNT(t2.IdReferensi) AS Total
    FROM master t1
    LEFT JOIN Transaction t2
    ON t2.IdReferensi LIKE t1.IdReferensi + ',%' OR         -- beginning of CSV
       t2.IdReferensi LIKE '%,' + t1.IdReferensi + ',%' OR  -- middle of CSV
       t2.IdReferensi LIKE '%,' + t1.IdReferensi            -- end of CSV
    GROUP BY t1.IdReferensi
) m2
    ON m1.IdReferensi = m2.IdReferensi

As a disclaimer, this query will not give reliable results if a given CSV value contains only a single number with no comma present. If you be worried about this edge case, we could fairly easily add logic to handle this.

Demo

9

solved Query SQL Server to get count data with populate string id another table