Gwasshoppa Gwasshoppa - 7 days ago 4
SQL Question

SQL STUFF() with UNION Query returning NULL

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

Answer

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??