String aggregation using STUFF

Question

Let's say I have table #DOC like this:

polNum	boxNo	batchNum
111111	null	qwe1234
111111	ff/55	rto1235
111111	ee/59	yhn1238
111111	ww/55	rto1235
222222	dd/58	jkl1234
222222	null	fgh1234
333333	null	asz1235
444444	ff/55	edc1234
444444	tt/58	qaz1234

This table doesn't have any primary key. We can assume that polNum and boxNo pair are unique.

I need have collection from batchNum and boxNo become like this:

polNum	boxNo	batchNum
111111	null
111111	ff/55	qwe1234,rto1235,yhn1238
111111	ee/59	qwe1234,rto1235,yhn1238
111111	ww/55	qwe1234,rto1235,yhn1238
222222	dd/58	jkl1234,fgh1234
222222	null
333333	null	asz1235
444444	ff/55	edc1234,qaz1234
444444	tt/58	edc1234,qaz1234

Need to manipulate batchNum row with comma separated based polNum and not empty boxNo and put same row. But if any condition boxNo is empty or null batchNum will put same polNum row.

I was tried using stuff approach, result still goes wrong:

SELECT DISTINCT polNum, boxNo ,
    STUFF((
        SELECT DISTINCT ','+batchNum
        FROM #DOC a
        WHERE a.polNum=d.polNum or a.boxNo = d.boxNo
        FOR XML PATH('')
    ),1,1,'') batchNum
FROM #DOC d 

polNum	boxNo	batchNum
111111	null	qwe1234,rto1235,yhn1238
111111	ff/55	qwe1234,rto1235,yhn1238
111111	ee/59	qwe1234,rto1235,yhn1238
111111	ww/55	qwe1234,rto1235,yhn1238
222222	dd/58	jkl1234,fgh1234
222222	null	jkl1234,fgh1234
333333	null	asz1235
444444	ff/55	edc1234
444444	tt/58	qaz1234

batchNum is not always be null, depends on value of boxNo and polNum. Null row have values and some not. depend on BoxNo value itself.

Answer 1

It's not clear what logic you are trying to achieve with regards to null rows, but it seems the logic might be:

• Same string aggregation on all rows per polNum
• If boxNo is null and there are other rows with the same polNum then replace the string aggregation with null

CREATE TABLE #Doc
    (polNum int, boxNo varchar(5), batchNum varchar(7))
;
    
INSERT INTO #Doc
    (polNum, boxNo, batchNum)
VALUES
    (111111,    null,   'qwe1234'),
    (111111, 'ff/55', 'rto1235'),
    (111111, 'ee/59', 'yhn1238'),
    (111111, 'ww/55', 'rto1235'),
    (222222, 'dd/58', 'jkl1234'),
    (222222, NULL, 'fgh1234'),
    (333333, NULL, 'asz1235'),
    (444444, 'ff/55', 'edc1234'),
    (444444, 'tt/58', 'qaz1234')
;


SELECT polNum, boxNo ,
    CASE WHEN boxNo IS NOT NULL OR
        COUNT(*) OVER (PARTITION BY polNum) = 1
    THEN
      STUFF((
        SELECT DISTINCT ','+batchNum
        FROM #DOC a
        WHERE a.polNum=d.polNum
        FOR XML PATH(''), TYPE
      ).value('text()[1]','nvarchar(max)'),1,LEN(','),'')
    END batchNum
FROM #DOC d 

db<>fiddle