sql - How can I restrict a query that uses STUFF to only return one record for each group of values stuffed rather than one for each stuffed item? -
with query:
select s.unit, lu.reportname, s.nextexecution, stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses, s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg reportsscheduler s full join reportslu lu on s.reportid = lu.reportid full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid order s.unit, s.reportid
...i want same count of records exists in rreportsscheduler table, in fact getting 1 record each related email address (emailaddr reportsunitemails).
where there 1 email address, 1 record returned; there 4 email addresses, 4 records returned; etc.
so question is: can "distinctify" complex query return 1 record each email address (while still stuffing them "allemailaddresses").
i tried this:
select s.unit, lu.reportname, s.nextexecution, distinct(stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses), s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg reportsscheduler s full join reportslu lu on s.reportid = lu.reportid full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid order s.unit, s.reportid
...but refuses comp[ile,ly], saying, "error 156: incorrect syntax near keyword 'distinct'. incorrect syntax near keyword 'for'."
not being sqlhead, shot in dark, anyway.
how can have stuff, fluffier, not stuffier? i'm wondering if self join might in future, don't know if that's so, , if so, how implement it.
update
both accepted answer , this:
select distinct s.unit, lu.reportname, s.nextexecution, s.reportid, stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses, s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg reportsscheduler s full join reportslu lu on s.reportid = lu.reportid full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid order s.unit, s.reportid
...work, i'm going accepted answer, seems "more better."
you need reportsunitemails
table inside subquery stuff
. commented out join reportsunitemails
in outer query. should work.
select s.unit, lu.reportname, s.nextexecution, stuff((select ','+emailaddr reportsunitemails e s.unit = e.unit , s.reportid = e.reportid xml path('')),1,1,'') allemailaddresses, s.nextexecutionsbegindatearg, s.nextexecutionsenddatearg reportsscheduler s full join reportslu lu on s.reportid = lu.reportid --full join reportsunitemails e on s.unit = e.unit , s.reportid = e.reportid order s.unit, s.reportid
Comments
Post a Comment