SSRS Column to contain list of many to many relationship items

Vote count: 0

I am looking to do a basic report and in my database, I have items in my main table representing hardware, software applications etc with a many to many relationship to the platform it belongs to.

In the event that a record has multiple platforms, I'd like it listen in a column separated by commas

Example of database layout

So Essentially I'd like to structure my query roughly as follows.

SELECT cat.CategoryName, sub.SubCategoryName, cur.Model, cur.Version, cur.Vendor, -- >pla.PlatformName,  <--
       cur.AvailableDate, cur.EndOfProduction, cur.EndOfSupport, dep.DepartmentName, 
       emp.FirstName + ' ' +emp.LastName AS 'Tech Owner', emp2.FirstName +' ' + emp2.LastName AS 'Tech Contact',
       cur.NumOfDevices, cur.UpgradeDuration, cur.FiscalConsideration
FROM
dbo.Currency Cur
INNER JOIN dbo.SubCategory sub
ON cur.SubCategoryId = sub.SubCategoryId
INNER JOIN dbo.Category cat
ON sub.CategoryId = cat.CategoryId
INNER JOIN dbo.Employee emp
ON cur.OwnerId = emp.EmployeeId
INNER JOIN dbo.Employee emp2
ON cur.ContactId= emp2.EmployeeId
INNER JOIN dbo.Department dep
ON cur.PortfolioOwnerId = dep.DepartmentId
LEFT JOIN dbo.Currency_Dependency cd
ON cur.CurrencyId = cd.CurrencyId
LEFT JOIN dbo.Currency_Affected ca
ON cur.CurrencyId = ca.CurrencyId
LEFT JOIN dbo.Currency_Platform cpla
ON cur.CurrencyId = cpla.CurrencyId
Left JOIN dbo.Platform pla
ON cpla.PlatformId = pla.PlatformId
GO

So where I have platform name in that query, I would like to structure it as that list separated by commas, but I have no idea how I would go about that. Would one of you be able to guide me on the right path should there be one? Id like to start with being able to do so through a basic SQL query if there is such a way. Ultimately I'd like to build a report through SSRS (using SSDT in Visual Studio 2015) through a parameter or a filter but I can look into that a bit more as I'm just learning it and am doing my reading on it.

Thank you so much in advance

asked 15 secs ago

David

Let's block ads! (Why?)

SSRS Column to contain list of many to many relationship items