Please I need help. I created a table
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.