sql – 连接带行的表

我试图插手SQL Server 2008 R2中的三个表,我但愿将第二个表中的项添加为新列.

具体表明 – 我有3个表：

第一个表包括用户名和用户ID

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

我不知道怎样实现这一点,任何辅佐将不胜谢谢.

办理要领

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 FIDDLE

可是,假如您但愿拥有动态数目的列,则必需行使动态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

SQL FIDDLE

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!