Getting multiple distinct values in rows into a single row, comma seperated

Question

This is my current query, using multiple othertables in this DB, but that is not important for this case:

SELECT        
    MAG_BANCHINE.BAN_BANCHINA AS POZICIJA, 
    MAG_BANCHINE.BAN_ORDINE AS NALOG, 
    DAT_ORDINI.ORD_RIGHETOT AS [# STAVKI], 
    STUFF(RUN_ESEGUI.ESE_UDC, 2, 3, '') AS MODULA, 
    SUM(DAT_ARTICOLI.ART_PMU * DAT_ORDINI_RIGHE.RIG_QTAR) AS MASA
FROM
    DAT_ORDINI 
INNER JOIN
    MAG_BANCHINE ON DAT_ORDINI.ORD_ORDINE = MAG_BANCHINE.BAN_ORDINE INNER JOIN
    DAT_ORDINI_RIGHE ON DAT_ORDINI.ORD_ORDINE = DAT_ORDINI_RIGHE.RIG_ORDINE 
INNER JOIN
    RUN_ESEGUI ON DAT_ORDINI_RIGHE.RIG_RIGA = RUN_ESEGUI.ESE_RIGA INNER JOIN
    DAT_ARTICOLI ON DAT_ORDINI_RIGHE.RIG_ARTICOLO = DAT_ARTICOLI.ART_ARTICOLO
GROUP BY 
    MAG_BANCHINE.BAN_BANCHINA, MAG_BANCHINE.BAN_ORDINE, 
    DAT_ORDINI.ORD_RIGHETOT, STUFF(RUN_ESEGUI.ESE_UDC, 2, 3, '')
ORDER BY 
    POZICIJA

Which gives me (with my current data in the other tables): Query

I want my all my distinct values in the "MODULA" column to be in only 1 row separated by commas, instead of each value to be in its own row (following the other rules I set regarding matching data).

Like this:

POZICIJA    NALOG           # STAVKI    MODULA    MASA
1           PTL TEST1              2      1, 2    0.0000000

I tried to use STRING_AGG in combination with STUFF like this:

SELECT        
    MAG_BANCHINE.BAN_BANCHINA AS POZICIJA, 
    MAG_BANCHINE.BAN_ORDINE AS NALOG, 
    DAT_ORDINI.ORD_RIGHETOT AS [# STAVKI],    
    STUFF(STRING_AGG(RUN_ESEGUI.ESE_UDC, ', '), 2, 3, '')  AS MODULA, 
    SUM(DAT_ARTICOLI.ART_PMU * DAT_ORDINI_RIGHE.RIG_QTAR) AS MASA
FROM 
    DAT_ORDINI 
INNER JOIN
    MAG_BANCHINE ON DAT_ORDINI.ORD_ORDINE = MAG_BANCHINE.BAN_ORDINE INNER JOIN
    DAT_ORDINI_RIGHE ON DAT_ORDINI.ORD_ORDINE = DAT_ORDINI_RIGHE.RIG_ORDINE 
INNER JOIN
                         RUN_ESEGUI ON DAT_ORDINI_RIGHE.RIG_RIGA = RUN_ESEGUI.ESE_RIGA  INNER JOIN
                         DAT_ARTICOLI ON DAT_ORDINI_RIGHE.RIG_ARTICOLO = DAT_ARTICOLI.ART_ARTICOLO
GROUP BY MAG_BANCHINE.BAN_BANCHINA, MAG_BANCHINE.BAN_ORDINE, DAT_ORDINI.ORD_RIGHETOT, STUFF(RUN_ESEGUI.ESE_UDC, 2, 3, '')
ORDER BY POZICIJA

But I am getting the same result as above.

Please help me use the funciton correctly.

Answer 1

I bet you need to sum before you use the string_agg. That way you can group by and sum the values already summed by modula.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE X(POZICIJA INT, MODULA INT, SUMVALUE INT)
INSERT X VALUES
(1,1,4),
(1,2,1),
(1,3,1),
(2,1,1),
(2,1,1),
(2,1,1),
(2,2,1),
(3,1,10)

Query 1:

;WITH Sums AS
(
    SELECT 
        POZICIJA,
        MODULA,
        SUM(SUMVALUE) AS SUMVALUE
    FROM 
        X
    GROUP BY
        POZICIJA,
        MODULA
)

SELECT 
    POZICIJA,
    STRING_AGG(MODULA,',') AS MODULA,
    SUM(SUMVALUE) AS SUMVALUE
FROM 
    Sums
GROUP BY
    POZICIJA

Results:

| POZICIJA | MODULA | SUMVALUE |
|----------|--------|----------|
|        1 |  1,2,3 |        6 |
|        2 |    1,2 |        4 |
|        3 |      1 |       10 |