问题描述
请帮助我.我创建了一个表Users(列UserID，UserName)和一个表Project(列ProjectID，ProjectName)和一个桥接表，其中显示了ProjectName和Username.
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.
桥表如下所示:
是否可以像这样以及如何制作日期视图?
Is it available to make the date view like this and how?
请注意，项目和用户名不是固定的，它们是动态的，它们是从VB.NET中的表单添加的.
Note the Projects and the UserNames are not fixed they are dynamic they add from forms in VB.NET.
推荐答案
我可以提出以下解决方案(请不要过于严格地判断，这只是一个主意):而不是使用虚拟的"Username"作为列名您的第一行，为什么不输入真实的用户名，并在每个用户/项目的行/列交叉处标上是/否标记.请参见下面的图片.这是各种成员资格表或球员计分表中的常见视图类型(但名称在垂直和水平上均相同).
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).
我插入了测试成员记录，因此您可以看到Mark是所有项目的成员，而Bryan仅在SQL中.
I have inserted test membership records so you see Mark is a member in all projects while Bryan only in SQL.
此动态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
注意，由于您的要求是拥有项目/用户的动态列表，因此我不得不使用动态sql创建T-SQL PIVOT列的动态列表，该列表通常与硬编码/静态值/列名一起使用.
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.
请注意，枢轴块中还使用了一个min(IsMember)技巧，因为枢轴需要包含一些聚合函数，但我们不能执行min(UserName)，因为它实际上会返回特定项目成员集合中的最小名称名称.
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.
@pivotColNamesMin是另一种技巧，现在用于GROUP BY，因为如果所有返回的列不在group by列表中或不在聚合函数中，则它将不会运行.
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.
我确定您可以将建议的数据源提供给数据网格，并且在某些ItemDataBound或类似事件期间，可以使用"YES"或任何此类内容将这些"1"成员标记更改为自定义字符串或图像.但是，为了使它起作用，您的databrid必须不要使用硬编码的列名，并且不能从实际结果集中动态加载它们.
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.
我已经在SQL Server 2008 R2上测试了查询.让我知道这是否是可以接受的解决方案，以便我可以停止对其进行调整.
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
这篇关于如何在SQL中将多对多实体关系转换为表格视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！