Search code examples
sqlsql-serverfor-xml-path

How to use Group By with where condition to concatenate column value in SQL Server

I want to return asset count as per type and also want to concatenate asset ids. I am using FOR XML and path which works fairly good but as soon as I add where clause, it does not work as expected.

This is my table schema and query:

CREATE TABLE [dbo].[Asset]
(
    [AssetSeqNumber] [bigint] NULL,
    [AssetType] [varchar](100) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (1, N'Tree')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (2, N'Tree')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (3, N'Tree')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (4, N'Barbecue')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (5, N'Bridge')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (101, N'Tree')

INSERT [dbo].[Asset] ([AssetSeqNumber], [AssetType]) 
VALUES (102, N'Tree')
GO

Query:

SELECT
    AssetType, 
    COUNT(AssetSeqNumber) AS count,
    STUFF((SELECT DISTINCT ',' + CAST(AssetSeqNumber AS varchar(100))
           FROM Asset
           WHERE AssetType = a.AssetType 
           FOR XML PATH ('')), 1, 1, '') AS AssetIds
FROM 
    Asset AS a
WHERE
    a.AssetSeqNumber IN (1, 2, 3, 4, 5)
GROUP BY 
    AssetType

enter image description here

This query return result for ids which are not in the where condition (i.e. 101,102). I understand it is because inner query check asset types but I can't figure out how to show expected result.

Note: I am using SQL Server 2019 (v15.0.2095.3 (X64))

Solution

  • You need to modify the where clause for the select statement inside the STUFF function as the following:

    WHERE AssetType = a.AssetType AND AssetSeqNumber IN (1, 2, 3, 4, 5)

    Also, for your version of SQL Server, you could simplify this by using STRING_AGG function as the following:

    SELECT AssetType, 
       COUNT(*) [Count],
       STRING_AGG(AssetSeqNumber, ',') AssetIds
FROM Asset
WHERE AssetSeqNumber IN (1, 2, 3, 4, 5)
GROUP BY AssetType

    See a demo for both queries.