Sample Header Ad - 728x90

String aggregation using STUFF

0 votes
1 answer
1826 views
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.
Asked by Garinda Burnama (3 rep)
Jun 6, 2021, 03:01 PM
Last activity: Jun 11, 2021, 11:28 AM