bbs-go-site

我有下面的架构和数据表。

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)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (2, N'abc', 1)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (3, N'bcd', 2)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (4, N'bcd', 2)
GO

表格数据：

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

我的最终结果应该像这样，

这是给出上述输出的查询，

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.

例如，

If I have NULL value in my integer Columns 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.

请协助我出问题了

SQL Server STUFF不适用于NULL值和分组