用行联接表

本文介绍了用行联接表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试联接SQL Server 2008 R2中的三个表，我希望将第二个表中的项目添加为新列.

I am trying to join three tables in SQL Server 2008 R2, where I want the items in the second table to be added as new column.

要详细解释-我有3个表:

To explain in detail - I have 3 tables:

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

First table contains User Name and User ID

UserID UserName 1 Mike 2 John 3 George

第二张表是带有职位名称的职位ID

Second Table is position ID's with Position Names

PositionID PositionName 1 RW 2 LW 3 DF 4 MDF 5 SS 6 CF etc

第三张表格包含他们的首选位置，一个用户可以拥有多个位置

Third table table contains their preferred positions where one user can have more than one

UserID PositionId 1 1 1 3 2 2 2 3 2 5 3 2 3 7

当我加入这些表时，我想为每个具有所有首选职位的用户提供单行

When I join these tables I want to get single row for every user with all the preferred positions like

UserID UserName PreferedPosition PreferedPosition2 PreferedPosition3 1 Mike RW LW 2 John CMF SS CF 3 George LW MDF

我不知道如何实现这一目标，我们将不胜感激.

I don't know how to achieve this, any help would be appreciated.

推荐答案

如果职位数量很少，则可以使用PIVOT关键字

If you have only a few numbers of positions, you can do it with PIVOT keyword

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，像这样

If, however, you want to have a dynamic number of columns, you have to use dynamic SQL, like this

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(''), type).value('.', 'nvarchar(max)'), 1, 2, '') select @stmt = ' select UserID, UserName,' + @stmt_columns2 + ' 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 (' + @stmt_columns1 + ') ) as PIV' exec sp_executesql @stmt = @stmt

SQL FIDDLE