我试图加入sql Server 2008 R2中的三个表,我希望将第二个表中的项添加为新列.
详细解释 – 我有3个表：
UserID UserName 1 Mike 2 John 3 George
第二个表是具有位置名称的位置ID
PositionID PositionName 1 RW 2 LW 3 DF 4 MDF 5 SS 6 CF etc
第三个表格包含一个用户可以拥有多个位置的首选位置
UserID PositionId 1 1 1 3 2 2 2 3 2 5 3 2 3 7
当我加入这些表时,我想为每个具有所有首选位置的用户获得单行
UserID UserName PreferedPosition PreferedPosition2 PreferedPosition3 1 Mike RW LW 2 John CMF SS CF 3 George LW MDF
我不知道如何实现这一点,任何帮助将不胜感激.
解决方法
如果您只有几个位置,可以使用PIVOT关键字来完成
select
UserID,UserName,[1] as Position1,[2] as Position2,[3] as Position3
from
(
select
U.UserID,U.UserName,P.PositionName,row_number() over (partition by U.UserID order by P.PositionName) as RowNum
from Positions_Users as PU
inner join Positions as P on P.PositionID = PU.PositionID
inner join Users as U on U.UserID = PU.UserID
) as P
pivot
(
min(P.PositionName)
for P.RowNum in ([1],[2],[3])
) as PIV
但是,如果您希望拥有动态数量的列,则必须使用动态sql,如下所示
declare @stmt nvarchar(max),@stmt_columns1 nvarchar(max),@stmt_columns2 nvarchar(max)
declare @Temp_Data table (RowNum nvarchar(max))
insert into @Temp_Data
select distinct row_number() over (partition by U.UserID order by P.PositionName) as RowNum
from Positions_Users as PU
inner join Positions as P on P.PositionID = PU.PositionID
inner join Users as U on U.UserID = PU.UserID
select @stmt_columns1 = stuff((select ',[' + RowNum + ']' from @Temp_Data for xml path(''),type).value('.','nvarchar(max)'),1,2,'')
select @stmt_columns2 = stuff((select ',[' + RowNum + '] as Position' + RowNum from @Temp_Data for xml path(''),'')
select @stmt = '
select
UserID,' + @stmt_columns2 + '
from
(
select
U.UserID,row_number() over (partition by U.UserID order by P.PositionName) as RowNum
from Positions_Users as PU
inner join Positions as P on P.PositionID = PU.PositionID
inner join Users as U on U.UserID = PU.UserID
) as P
pivot
(
min(P.PositionName)
for P.RowNum in (' + @stmt_columns1 + ')
) as PIV'
exec sp_executesql @stmt = @stmt
