Can STUFF function return NULL values?

Question

I'm looking at the following link:

Can I Comma Delimit Multiple Rows Into One Column?

The query used in the solution is exactly what I need, but also need to return NULL values.

SELECT t.TicketID,
   STUFF(ISNULL((SELECT ', ' + x.Person
            FROM @Tickets x
           WHERE x.TicketID = t.TicketID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') [No Preceeding Comma]
FROM @Tickets t
GROUP BY t.TicketID

This works great if each [TicketID] has an associated [Person].

But if the Person is NULL, for the TicketID, I need to return just the TicketID and have the Person show as NULL or blank. I'm having trouble removing the ISNULL cleanly.

From this:

[TicketID], [Person]
 T0001       Alice
 T0001       Bob
 T0002       Catherine
 T0002       Doug
 T0003       Elaine
 T0004       NULL

To this:

[TicketID], [Person]
 T0001       Alice, Bob
 T0002       Catherine, Doug
 T0003       Elaine
 T0004       NULL

You need to write `PIVOT` on top of your query... hope you can explore — techspider, Jul 28 '16 at 20:47
Is this a question? Or attempts at discussion? Lookup MSDN, and go forward methodically. Also, there is no reason you cannot extract, transform, and then use your data. Simplify the process and it might help finding ways to optimize. — clifton_h, Jul 28 '16 at 20:56

Gordon Linoff · Answer 1 · 2016-07-28T23:14:25.063

0

Is this what you want?

SELECT t.TicketID,
        STUFF((SELECT ', ' + COALESCE(t2.Person, '<NULL>')
               FROM @Tickets t2
               WHERE t2.TicketID = t.TicketID
               FOR XML PATH (''), TYPE
              ).value('.', 'VARCHAR(max)'
                     ), 1, 2, ''
             ) as Persons
FROM @Tickets t
GROUP BY t.TicketID;

The GROUP BY in the subquery is not necessary, unless you have duplicates.

edited Jul 28 '16 at 23:14

answered Jul 28 '16 at 21:04

Gordon Linoff

1,242,037
58
646
786

I'm receiving the following error: "The stuff function requires 4 argument(s)." – bojackh Jul 28 '16 at 21:09
@bojackh . . . Ooops, I left your original syntax in the answer for that portion of the code. The above should be the correct formulation. – Gordon Linoff Jul 28 '16 at 22:40
Thanks, looks like we needed to take out the , '') – bojackh Jul 28 '16 at 23:11
Hmm....it's still not returning the NULL. I played with it a bit more and was able to get it to work by left joining the entire query above on the TicketID. Probably not optimal but at least works... – bojackh Jul 28 '16 at 23:32

score 0 · Answer 2 · answered Aug 16 '16 at 09:07

I cannot see your problem... This should work out-of-the-box. Maybe I did not understand the problem...

removed the NOT NULL for Person
added one more line with TicketID="T0004" and a NULL as person
removed the ISNULL from your query
simplified your code

Try this:

DECLARE @Tickets TABLE (
    [TicketID] char(5) NOT NULL,
    [Person] nvarchar(15)  NULL
);

INSERT INTO @Tickets VALUES
    ('T0001', 'Alice'),
    ('T0001', 'Bob'),
    ('T0002', 'Catherine'),
    ('T0002', 'Doug'),
    ('T0003', 'Elaine'),
    ('T0004', NULL);

SELECT t.TicketID,
   STUFF(
           (
            SELECT ', ' + x.Person
            FROM @Tickets x
            WHERE x.TicketID = t.TicketID
            FOR XML PATH ('')
            ),1,2,'') AS [No Preceeding Comma]
FROM @Tickets t
GROUP BY t.TicketID;

The result

TicketID   No Preceeding Comma
T0001      Alice, Bob
T0002      Catherine, Doug
T0003      Elaine
T0004      NULL

Can STUFF function return NULL values?

2 Answers2