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.
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
Thank you guys I found other solution by select the project name from combo-box and in the table show the names of the users that they are in this projects its not looks like what i want in the first but this solution more easy :D thank you again
I created a table in SQL Server with the entries you showed, and a VB.NET console application with this code:
Option Infer On
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim csb As New SqlConnectionStringBuilder With {.DataSource = ".\SQLEXPRESS", .InitialCatalog = "testing", .IntegratedSecurity = True}
Dim projectsUsers As New Dictionary(Of String, List(Of String))
Using sqlConn As New SqlConnection(csb.ConnectionString)
Dim sql = "SELECT [project], [user] FROM ProjectUser"
Using sqlCmd As New SqlCommand(sql, sqlConn)
sqlConn.Open()
Using rdr = sqlCmd.ExecuteReader()
If rdr.HasRows Then
While rdr.Read()
Dim project = rdr.GetString(0)
Dim user = rdr.GetString(1)
If projectsUsers.ContainsKey(project) Then
projectsUsers(project).Add(user)
Else
projectsUsers.Add(project, New List(Of String) From {user})
End If
End While
End If
End Using
End Using
End Using
' adjust output as required
For Each kvp In projectsUsers
Console.WriteLine(kvp.Key & ": " & String.Join(", ", kvp.Value))
Next
Console.ReadLine()
End Sub
End Module
and got this output:
SQL: Michael, Sam, Mark
C#: Sam, Mark
Joomla: Mark
You just need to modify the method to display the output as desired.
