SQL STUFF() with UNION Query returning NULL

Question

I have a SQL Server 2012 query that is returning 1 row of data , BUT could return multiple rows, based on the query.

I want to turn the following data into a csv concatenated string. My query uses STUFF() with a UNION query which I based off this Stack Overflow question.

i.e: my data would be this

MR
------
NS
------

and would become:

MR, NS

When I select the data without the STUFF() part of the query, it returns data no problem.

The full query (see below) runs without errors but always returns NULL even though the SELECT UNION part of the query returns data.

My full query looks like this:

SELECT STUFF((Select * FROM (
SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
FROM personenc INNER JOIN contacts ON personenc.cntid = contacts.cntid INNER JOIN scddisc ON contacts.discipline = scddisc.disid
WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) --AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
UNION 
SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
FROM scddisc INNER JOIN contacts ON scddisc.disid = contacts.discipline INNER JOIN personenc ON contacts.cntid = personenc.ocntid
WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) -- AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
) AS k
FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,'')

Can anyone let me know what I have done wrong?

The query that works is the SELECT UNION part of the above query i.e. when I run the query below it returns valid data.

SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
    FROM personenc INNER JOIN contacts ON personenc.cntid = contacts.cntid INNER JOIN scddisc ON contacts.discipline = scddisc.disid
    WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) --AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
    UNION 
    SELECT ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
    FROM scddisc INNER JOIN contacts ON scddisc.disid = contacts.discipline INNER JOIN personenc ON contacts.cntid = personenc.ocntid
    WHERE (personenc.perid = 15410) AND (scddisc.active = 1) AND (contacts.active = 1) AND (scddisc.medgroup = 1) -- AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')

However when combined with the FOR XML and STUFF, then the full statement return NULL

It's not `STUFF` that does concatenation. It's `FOR XML PATH('')`. Can you show the query that works? — Szymon, Mar 18 '14 at 01:56
Have added the select query that works... it is basically the SELECT UNION part of the full query. — Gwasshoppa, Mar 18 '14 at 02:21

score 2 · Accepted Answer · edited Dec 01 '16 at 16:10

Interestingly enough I have found the issue. It appears to be 2 fold...

It appears that the .value('text()[1]','nvarchar(max)'),1,2,'') part of the STUFF query was the part that was not displaying data.

When I change the text() to a . i.e. .value('.[1]','nvarchar(max)'),1,2,'') the query returns as expected.
Secondly I had forgotten to add the N', ' + to the ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines to make it a comma separated list as required.

Therefore the whole new statement became:

SELECT
    STUFF (
        (
            SELECT
                *
            FROM
                (
                    SELECT
                        N', ' + ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
                    FROM
                        personenc
                    INNER JOIN contacts ON personenc.cntid = contacts.cntid
                    INNER JOIN scddisc ON contacts.discipline = scddisc.disid
                    WHERE
                        (personenc.perid = 15410)
                    AND (scddisc.active = 1)
                    AND (contacts.active = 1)
                    AND (scddisc.medgroup = 1) --
                    AND (
                        personenc.sdate BETWEEN '2014-03-05'
                        AND '2014-03-12'
                    )
                    UNION
                        SELECT
                            N', ' + ISNULL(scddisc.abbrev, '!!') AS medicalDisciplines
                        FROM
                            scddisc
                        INNER JOIN contacts ON scddisc.disid = contacts.discipline
                        INNER JOIN personenc ON contacts.cntid = personenc.ocntid
                        WHERE
                            (personenc.perid = 15410)
                        AND (scddisc.active = 1)
                        AND (contacts.active = 1)
                        AND (scddisc.medgroup = 1) -- AND (personenc.sdate BETWEEN '2014-03-05' AND '2014-03-12')
                ) AS k FOR XML PATH (''),
                TYPE ).
            VALUE
                ('.[1]', 'nvarchar(max)'),
                1,
                2,
                ''
        );

Can anyone tell me what the meaning/difference of .value('.[1]','nvarchar(max)'),1,2,'') and .value('text()[1]','nvarchar(max)'),1,2,'') is??

score 0 · Answer 2 · answered Mar 18 '14 at 02:30

0

SELECT STUFF (
(SELECT ... FOR XML PATH ('')), 1, 2, '')

answered Mar 18 '14 at 02:30

Squirrel

23,507
4
34
32

How does this help? What is it doing? – Jojodmo Mar 18 '14 at 02:50
This does not help me... That is what my above query is doing... I want to know why I would be getting NULL from the STUFF() & FOR XML when I combine it with the UNION query. My Full query in my question is returning NULL however the SELECT UNION query is returning data when I run it outside of the STUFF() and FOR XML() – Gwasshoppa Mar 18 '14 at 03:16

SQL STUFF() with UNION Query returning NULL

2 Answers2