STUFF doesn’t work well with NULL Values and Grouping

I have table with below schema and data.

CREATE TABLE [dbo].[SearchTest]
(
    [DocumentNumber] [int] NOT NULL,
    [AlphaNumeric] [nvarchar](50) NULL,
    [Integers] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) 
VALUES (1, N'abc', 1)

INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) 
VALUES (2, N'abc', 1)

INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) 
VALUES (3, N'bcd', 2)

INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) 
VALUES (4, N'bcd', 2)
GO

Table data:

I would like to do grouping using Alphanumeric and Integers column and get the DocumentNumber as comma separated value in my final result.

My final result should look like this,

Here is my query that gives the above output,

SELECT *
FROM
    (SELECT
         STUFF((SELECT ', ' + CAST(DocumentNumber AS VARCHAR(10)) [text()] 
                FROM SearchTest 
                WHERE AlphaNumeric = Result.Alphanumeric 
                   OR Integers = Result.Integers 
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') DocumentNumbers, 
        COUNT(DocumentNumber) TotalDocuments, 
        Result.AlphaNumeric, 
        Result.Integers 
    FROM
        (SELECT * 
         FROM SearchTest 
         WHERE AlphaNumeric LIKE '%b%' OR Integers = 1) AS Result 
    GROUP BY 
        Result.AlphaNumeric, Result.Integers) AS Final

However the above query breaks if I have null values in Integers column.

For example, if I have NULL value in my integer columns as shown here:

Now my query breaks and I get the wrong results in my stuff query as shown below

Grouping works fine in the above query but STUFF part which gives DocumentNumbers gives wrong result. In this case it has be 2 in first row and 1 in second row.

Here is the expected result:

| DocumentNumbers| TotalDocuments| AlphaNumeric  | Integers      |
+----------------+---------------+---------------+---------------+
| 2              | 1             | abc           | NULL          |
| 1              | 1             | abc           | 1             |
| 3, 4           | 2             | bcd           | 2             |

Please assist on where I’m going wrong

Answer

Following @GordonLinoff comments in the question. This can be easily achieved using STRING_AGG() provided you’re using SQL Server 2017 and above. This simplifies the query as well.

Query:

SELECT *
FROM
    (SELECT 
        STRING_AGG(Result.DocumentNumber, ', ') DocumentNumbers, 
        COUNT(DocumentNumber) TotalDocuments, 
        Result.AlphaNumeric, 
        Result.Integers
    FROM
        (SELECT * 
         FROM SearchTest 
         WHERE AlphaNumeric LIKE '%b%' OR Integers = 1) AS Result 
    GROUP BY 
        Result.AlphaNumeric, Result.Integers) AS Final

Expected Output:

Advertisement

Answer