sql - 多对多成一排

Question

我在 SQL Server 2008 R2 中有一个类似于下面的“表”的视图。

我想要一个“交叉表”视图或存储过程来获得如下所述的结果。该视图由三个表 Main --- to many Props --- to many lineitem

Mainname | Prop | lineitems
---------+------+------------
Smith    | P1   | cheese  
Smith    | P1   | tires  
Smith    | P1   | baseballs  
Smith    | P2   | gel  
Smith    | P3   | windows  
Smith    | P3   | guitar  
Jones    | T4   | shoes  
Lane     | Q1   | cushion  
Lane     | Q2   | dirt

我需要让它看起来像这样：

Mainname | Prop1 | lineitems1 | lineitems2 | lineitems3 | Prop2 | lineitems1 | Prop3 | lineitems1 | lineitems2
---------+-------+------------+------------+------------+-------+------------+-------+------------+-----------
Smith    | P1    | cheese     | tires      | baseballs  | P2    | gel        | P3    | windows    | guitar  
Jones    | T4    | shoes  
Lane     | P5    | cushion    | <null>     | <null>     | P6    | dirt  

标签： sqlsql-serverone-to-many

Answer 1

虽然拉努的回答可能有效（我没有尝试过）。对我有用的解决方案是这样的。我在表格中添加了另一列...成本

declare @Count int,@Cur int
declare @Qry nvarchar(4000)
set @Cur=1

select @Count=max(t.count) from (

select proposal, count(lineitems) count from view_1 group by proposal) t

set @Qry=''

while @Cur<@Count+1
begin
set @Qry= @Qry + ',(select t.lineitems from (select ROW_NUMBER() over(order by lineitems) ROW_NUMBER, 
lineitems from view_1 where proposal = t1.proposal) t where ROW_NUMBER='+ cast(@Cur as varchar(10)) + ') as lineitem' + cast(@Cur as varchar(10))
set @Qry= @Qry + ',(select t.costs from (select ROW_NUMBER() over(order by lineitems) ROW_NUMBER, 
costs from view_1 where proposal = t1.proposal) t where ROW_NUMBER='+ cast(@Cur as varchar(10)) + ') as costs' + cast(@Cur as varchar(10))

set @Cur=@Cur+1
end

set @Qry='select distinct t1.grantname, t1.proposal ' + @Qry + ' from view_1 t1 order by 1, 2'

print @Qry
exec sp_executesql @Qry