Multiple groupby with comma separated values in SQL Server

Question

I had the following table structure which is listed below

Budhol    COCODE    BEN      OBJ    SPARE2  SPARE1  TASKNO  Value   Field   Code
---------------------------------------------------------------------------------
362103    36        362101  991003  NULL    MA1001  NULL    4516    613030  001
362104    36        362104  991003  NULL    MA1001  NULL    9088    613030  002
362103    36        362101  991003  NULL    MA1001  NULL    3387    613030  003
362103    36        362101  991003  NULL    MA1001  NULL    4026    613030  004

This is the required output

Budhol  COCODE  BEN      OBJ    SPARE2  SPARE1  TASKNO  Value   Field   Code
---------------------------------------------------------------------------------
362103  36      362101  991003  NULL    MA1001  NULL    11929   613030  001,003,001
362104  36      362104  991003  NULL    MA1001  NULL    9088    613030  002

i.e By doing group by i need sum of all values and comma separated Codes

I had tried below query but output is not matching im getting all the codes for all the columns

SELECT 
    B.BEN, B.Budhol, B.COCODE, B.FIELD, B.OBJ, B.SPARE1, B.SPARE2,    
    SUM(B.value) AS Value ,
    Code = STUFF((SELECT ', ' + Code 
                  FROM #temp2 b 
                  WHERE b.BEN = B.BEN 
                    AND b.Budhol = B.Budhol 
                    AND b.COCODE = B.COCODE 
                    AND b.FIELD = B.FIELD  
                    AND b.SPARE1 = B.SPARE1 
                    AND b.SPARE2 = B.SPARE2 
                  FOR XML PATH('')), 1, 1, '')
FROM 
    #temp2 B
GROUP BY 
    B.BEN, B.Budhol, B.COCODE, B.FIELD, B.OBJ, B.SPARE1, B.SPARE2;

Possible duplicate of I want to do group_concat in SQL Server — Carsten Massmann
– Carsten Massmann, Commented Aug 2, 2018 at 6:04
@cars10mi had tried the same you can check my query which i had tried — Nilesh Gajare
– Nilesh Gajare, Commented Aug 2, 2018 at 6:08
@Nilesh please check the answer i have posted and you accepted answer will not work please check — Chanukya
– Chanukya, Commented Aug 2, 2018 at 6:19

Tim Biegeleisen · Accepted Answer · 2018-08-02 06:18:01Z

The problem appears to have to do with the NULL values in some of your columns. In particular, the SPARE1 and SPARE2 columns can take on NULL values, and your intention is that such NULL values should equate. In the query below, I wrapped them on both sides of the join with COALESCE, and I am getting the expected output now.

SELECT B.BEN, B.Budhol, B.COCODE, B.FIELD, B.OBJ, B.SPARE1, B.SPARE2,    
    SUM(B.value) AS Value,
    Code = STUFF((SELECT ', ' + Code FROM temp2 t
                  WHERE t.BEN = B.BEN AND t.Budhol = B.Budhol AND t.COCODE = B.COCODE AND
                        t.FIELD = B.FIELD AND
                        COALESCE(t.SPARE1, '') = COALESCE(B.SPARE1, '') AND
                        COALESCE(t.SPARE2, '') = COALESCE(B.SPARE2, '')
                 FOR XML PATH('')), 1,1, '')
FROM temp2 B
GROUP BY B.BEN, B.Budhol, B.COCODE, B.FIELD, B.OBJ, B.SPARE1, B.SPARE2;

Demo

Marc Guillot · Accepted Answer · 2018-08-02 10:42:18Z

2

You are using the same alias (B) for the inner and the outer query. You have to use different alias (B1 & B2) to differentiate them :

SELECT B1.BEN, B1.Budhol, B1.COCODE, B1.FIELD, B1.OBJ, B1.SPARE1, B1.SPARE2,    
       SUM(B1.value) as Value ,
       STUFF((SELECT ', ' + Code 
              FROM #temp2 b2 
              WHERE b2.BEN = B1.BEN and b2.Budhol = B1.Budhol and b2.COCODE = B1.COCODE and b2.FIELD = B1.FIELD and 
                    isnull(b2.SPARE1,'') = isnull(B1.SPARE1,'') and isnull(b2.SPARE2,'') = isnull(B1.SPARE2,'')
              FOR XML PATH('')), 1,1, '') as Code
FROM #temp2 B1
GROUP BY B1.BEN,B.Budhol, B1.COCODE, B1.FIELD, B1.OBJ, B1.SPARE1, B1.SPARE2;

Update: Additionally, as Tim Biegeleisen has seen, b2.spare2 = b1.spare2 will return false when both are null (null is an empty state, not a value), so you should convert those null states to some values that can be compared, using the coalesce or isnull functions : isnull(b2.spare2,'') = isnull(b1.spare2,'')

edited Aug 2, 2018 at 10:42

answered Aug 2, 2018 at 6:13

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

3 Comments

Marc Guillot Over a year ago

@Chanukya Are you sure, I don't see why should it give an error. The OP has accepted the answer.

Marc Guillot Over a year ago

@Nilesh, Yes, I saw the obvious error (aliases), but I didn't try your data and I didn't see that you had null values. Glad to have been of help.

Chanukya Over a year ago

@Nilesh check my answer

Chanukya · Accepted Answer · 2018-08-02 06:16:05Z

CREATE TABLE #Table1
    ([Budhol] int, [COCODE] int, [BEN] int, [OBJ] int, [SPARE2] varchar(4), [SPARE1] varchar(6), [TASKNO] varchar(4), [Value] int, [Field] int, [Code] varchar(100))
;

INSERT INTO #Table1
    ([Budhol], [COCODE], [BEN], [OBJ], [SPARE2], [SPARE1], [TASKNO], [Value], [Field], [Code])
VALUES
    (362103, 36, 362101, 991003, NULL, 'MA1001', NULL, 4516, 613030, 001),
    (362104, 36, 362104, 991003, NULL, 'MA1001', NULL, 9088, 613030, 002),
    (362103, 36, 362101, 991003, NULL, 'MA1001', NULL, 3387, 613030, 003),
    (362103, 36, 362101, 991003, NULL, 'MA1001', NULL, 4026, 613030, 004)

SELECT DISTINCT BUDHOL
    ,COCODE
    ,BEN
    ,OBJ
    ,SPARE2
    ,SPARE1
    ,TASKNO
    ,SUM(VALUE)VALUE
    ,FIELD
    ,STUFF((
            SELECT ','+'00' + CONVERT( VARCHAR(10),U.CODE)
            FROM #TABLE1 U
            WHERE B.BEN = U.BEN
                AND B.BUDHOL = U.BUDHOL
                AND B.COCODE = U.COCODE
                AND B.FIELD = U.FIELD
                AND B.SPARE1 = U.SPARE1


            FOR XML PATH('')
            ), 1, 1, '') AS USERLIST
FROM #TABLE1 B
GROUP BY [BUDHOL]
    ,[COCODE]
    ,[BEN]
    ,[OBJ]
    ,[SPARE2]
    ,[SPARE1]
    ,[TASKNO]
    ,[FIELD]

output

BUDHOL  COCODE  BEN OBJ SPARE2  SPARE1  TASKNO  VALUE   FIELD   USERLIST
362103  36  362101  991003  NULL    MA1001  NULL    11929   613030  001,003,004
362104  36  362104  991003  NULL    MA1001  NULL    9088    613030  002

Collectives™ on Stack Overflow

Multiple groupby with comma separated values in SQL Server

3 Answers 3

Demo

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related