I posted this a while back: Can't seem to understand why STUFF isn't working and @bluefeet was able to help me out very graciously...
Now I've got a similiar issue with this STUFF()! Basically I have three tables one table has Company names Company
. Another table has Issues Issue
and finally the third table has Issues that happened in a Company, call it PAndCIssueLocation
.
For instance assume you have an issue that happened at two plants...so you end up with one Issue
record like so:
Issue Table
ID Title
1 Robot is down
And you've got a company table:
Company Table
ID Name
1 Auburn Hills
2 Detroit
And finally the third table:
IssueID CompanyID
1 1
1 2
That is Issue ID = 1 happened at two company locations (Detroit, Auburn Hills). I was trying to use STUFF in a report so I can comma seperate these two items (company names) however, what is happening is the same company name is repeating itself X amount of times (I believe X is the total number of issues in my database). Here is the code I have:
SELECT
i.IssueID AS IssueID,
i.PAndCAuditDate AS AuditDate,
i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
i.CustomerIssueNumber AS OldId,
i.PAndCValidatedBy AS ValidatedBy,
i.PAndCValidatedComments AS ValidatedComments,
i.Title AS Title,
i.OpenDate AS IssueOpenDate,
i.PAndCAuditors AS Auditors,
i.PAndCAuditees AS Auditees,
i.Description AS Description,
i.PAndCRootCause AS RootCause,
i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
i.PAndCPreventiveActions AS PreventiveActions,
DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
i.TargetDate AS IssueTargetDate,
i.ClosedDate AS IssueClosedDate,
CASE WHEN i.ClosedDate IS NULL AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0) < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0) THEN 1 ELSE 0 END AS IsOverdue,
s1.CompanyHasType
--s1.Name
FROM
Issue i
LEFT JOIN
(
SELECT c.IssueID, STUFF((
SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
INNER JOIN PAndCIssueLocation cht
ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
ON c.IssueID = cht.IssueID
FOR XML PATH(''), TYPE).value('.[1]',
'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID
Notice this portion where the STUFF is happening:
LEFT JOIN
(
SELECT c.IssueID, STUFF((
SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
INNER JOIN PAndCIssueLocation cht
ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
ON c.IssueID = cht.IssueID
FOR XML PATH(''), TYPE).value('.[1]',
'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID
This is the portion that is giving me an issue. I know I have to somehow relate the issue back but I don't know exactly how!
Any help is much appreciated...