How to convert many-to-many entity relationship to tabular view in SQL?

Question

Please I need help. I created a table Users (columns UserID, UserName) and a table Project (columns ProjectID, ProjectName) and a bridge table which shows the ProjectName and Username.

Each project has more than one user, and each user can be in more than one projects.

The bridge table looks like this:

Is it available to make the date view like this and how?

Note the Projects and the UserNames are not fixed they are dynamic they add from forms in VB.NET.

Answer 1

may I propose the following solution (please, don't judge too strictly, it's just an idea): instead of having dummy 'Username' as column name in your first row, why not put there real user names and have yes/no marks on the row/column crossing for each user/project. See the picture below. This is a common view type in various membership tables or player scoring tables (but those have equal names vertically and horizontally).

I have inserted test membership records so you see Mark is a member in all projects while Bryan only in SQL.

This dynamic SQL produces the above output:

DECLARE @dynPivotSQL AS NVARCHAR(MAX)
DECLARE @pivotColNames AS NVARCHAR(MAX)
DECLARE @pivotColNamesMin AS NVARCHAR(MAX)

-- get distinct username for column headers
SELECT @pivotColNames= ISNULL(@pivotColNames + ',','') 
       + QUOTENAME(UserName),
       @pivotColNamesMin= ISNULL(@pivotColNamesMin + ',','') 
       + 'min(' + QUOTENAME(UserName) +') as ' + QUOTENAME(UserName)
FROM (SELECT DISTINCT UserName, UserID FROM Users) AS U

SET @dynPivotSQL = N'
with cte as (
    select p.ProjectName,u.UserName,IsMember=1 from ProjectsUsers pu
    join Projects p on p.ProjectID = pu.FK_ProjectID
    join Users u on u.UserID = pu.FK_UserID
) select ProjectName, ' + @pivotColNamesMin + ' from cte
pivot (
    min(IsMember) for UserName in (' + @pivotColNames + ')
) pvt
group by ProjectName '          

EXEC sp_executesql @dynPivotSQL

Note, since your requirement is to have dynamic lists of projects/users I had to use dynamic sql to create the dynamic list of columns for T-SQL PIVOT which normally works with hardcoded/static values/column names.

Notice also there is a min(IsMember) trick in the pivot block used because pivot requires some aggregate function included but we can't do min(UserName) because it will really return the minimal name in the set of particular project member names.

The @pivotColNamesMin is another trick, now for GROUP BY because it won't run if all returned columns are not in the group by list or are not inside an aggregate function.

I'm sure you can feed the proposed datasource to your data grid and during some ItemDataBound or similar events you can change these '1' memberhip marks to custom strings or images with 'YES' or anything of this sort. But in order for this to work your databrid must not use hardcoded column names and be able to load them dynamically from the actual results set.

I've tested my query on SQL Server 2008 R2. Let me know if this is an acceptable solution so that I may stop tweaking it.

HTH