I'm modifying a stored procedure which populates a MyInsights report. That M.I report is opened as a drill to detail report from a 3D Interface.
The concatentation is intended to ensure that a unique row is returned. For example, if I want to return results in the following format:
ID | LName | Vehicle |
1 | Doe | Honda , Ford |
2 | Johnson | BMW, Audi |
What I don't want to return is the following
ID | LName | Vehicle |
1 | Doe | Honda |
2 | Doe | Ford |
3 | Johnson | BMW |
4 | Johnson | Audi |
The SQL being used is listed: (Please note the above results are not directly related to the SQL listed)
Cross Apply
(
select REPLACE(
(
select dsg.ShortDescription +', '
from Final.FactStudentGroups fsg
join final.dimstudentgroup dsg
on fsg.StudentGroupKey = dsg.studentgroupkey
where fsg.VersionKey = 1
and fsg.TermSourceKey = SDDT.TermSourceKey
and fsg.EmployeeID = SDDT.EmployeeID
and fsg.VersionKey = SDDT.VersionKey
FOR XML PATH('')
),'&','&') as SGroup -- Not sure why code to the left is commented but it should not be.
)StudentGroup
As of right now I'm returning data that I don't even recognize from my dsg.Table which is the table I'm attempting to pull data from.
Any information is appreciated! Thanks in advance.