There must be a way to Group By 1 column without including every column. I don't want to Group By t1.id or t2.id, only t1.Company_Name .
Note: CTE is just an idea that might work, I don't have CTE code here.
Note: I have more columns not included in this example for brevity sake...
Company_Name States Contact_Name
My Business KY; OH John Doe
My Business KY; OH Mark Smith
My Business KY; OH Bill Jones
My Business KY; OH Dan Moore
Stuff ((SELECT '; ' + s.name AS [text()]
FROM States s
WHERE (t1.id = s.id) FOR XML path('')), 1, 1, '') AS [States],
Stuff ((SELECT '; ' + c.Contact_Name AS [text()]
FROM Contacts c
WHERE (t2.id = c.id) FOR XML path('')), 1, 1, '') AS [Contacts],
Join Contacts t2 on t1.id = t2.ContactID
t1.Company_Name ], --I only want to Group on this column
t1.id, --don't want to group by
t2.id --don't want to group by