TSQL Return Multiple Rows to Column - If empty Return 0
Looking for SQL String to combine 1 field from many records. If no records found need to return a 0.
Below is what I have so far and it works EXCEPT
If a person has no records in prod_Materials - I need it to return a 0
SELECT LTRIM(STR(prodID)) + ',' AS 'data()'
from prod_Materials
where personID = 'B1970'
Group by prodID
order by prodID
FOR XML PATH ('')
Any ideas?
Thanks
JS
Below is what I have so far and it works EXCEPT
If a person has no records in prod_Materials - I need it to return a 0
SELECT LTRIM(STR(prodID)) + ',' AS 'data()'
from prod_Materials
where personID = 'B1970'
Group by prodID
order by prodID
FOR XML PATH ('')
Any ideas?
Thanks
JS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to return a row when there is no matching rows in the [prod_Materials] table, you can create a row as a subquery (instead of using a where clause) then join to that. Then use ISNULL() or COALESCE(), to show "0" if there is no match:
SELECT
ISNULL(oa1.data,'0') AS [data]
FROM (
SELECT
'B1970' AS personID
) p
OUTER APPLY (
SELECT
LTRIM( STR( prodID ) ) + ',' AS [data]
FROM prod_Materials pM
WHERE pM.personID = p.personID
GROUP BY
prodID
ORDER BY
prodID
FOR xml PATH ('')
) AS oa1
I really would not recommend using parentheses in a column name, but if you are going to use such an unusual column name I suggest you use brackets not single quotes e.g.
SELECT LTRIM(STR(prodID)) + ',' AS [data()]
this will stand out more clearly as a column heading and not just another literal.
ps. identities are typically quoted by "" or in TSQL with [] , single quotes are usually reserved for use with literals.