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

Question

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

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))

Answer 1

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.

Answer 2

your data

drop table if exists #Asset
CREATE TABLE #Asset
(
    [AssetSeqNumber] [bigint] NULL,
    [AssetType] [varchar](100) NULL
) ON [PRIMARY]
GO

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

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

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

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

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

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

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

you should add your condition into your Xml query

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

by consider using SQL Server 2019,you can use string_agg

SELECT [assettype],
       Count(assetseqnumber)           count,
       String_agg(assetseqnumber, ',') AssetIds
FROM   #asset a
WHERE  a.assetseqnumber IN ( 1, 2, 3, 4, 5 )
GROUP  BY [assettype]