SQL Server – String Aggregation Using STUFF

sql serversql-server-2016string-aggregation

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.

Best Answer

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

Related Question