一对多表的行转列问题！！

[wxf87541163]

表1：ProductPrice（产品价格）
ID | UserID | ProductID | WriteTime
----|--------|-----------|---------
1 | 1 | 2 | 2007-12-6
2 | 1 | 2 | 2007-12-7
3 | 2 | 1 | 2007-12-6
4 | 2 | 3 | 2007-12-1

表2：ProductPriceInfo(价格详情)

ProductPriceID | Area | Price
----------------|------|--------
1 | 杭州 | 30
1 | 上海 | 130
2 | 杭州 | 20
2 | 上海 | 13
3 | 武汉 | 34

表3：UserArea(用户地区)
ID | UserID | Area
----|--------|-----
1 | 1 | 杭州
2 | 1 | 上海
3 | 2 | 武汉

我现在想写个存储过程通过UserID和ProductID查询出这样的一张表:
UserID=1 and ProductID=2

WriteTime | 杭州 | 上海
-----------|-----|------
2007-12-6 | 30 | 130
2007-12-7 | 20 | 13

请问各位高手们应该怎么写这个存储过程啊？？

[晓风残月0110]

create table pro(writetime datetime,area nvarchar(10),price int)
insert into pro select '2007-12-6','杭州',30
insert into pro select '2007-12-6','杭州',130
insert into pro select '2007-12-7','上海',20
insert into pro select '2007-12-7','上海',13

declare @sql varchar(8000)
set @sql = 'select writetime'
select @sql = @sql + ',' + area+ '=sum(case area when ''' + area+ ''' then price else 0 end)'
from pro group by area order by area
set @sql = @sql + ' from pro group by writetime'
exec(@sql)
writetime               杭州          上海
----------------------- ----------- -----------
2007-12-06 00:00:00.000 160         0
2007-12-07 00:00:00.000 0           33

[wxf87541163]

请问（晓风残月）大哥 能不能写个存储过程啊 因为我是通过ProductPrice表里面的UserID和ProductID作为条件来查询的，比如：Where UserID = 1 and ProductID = 2 就可以查询出这样的结果
WriteTime ¦ 杭州 ¦ 上海
2007-12-6 ¦ 30 ¦ 130
2007-12-7 ¦ 20 ¦ 13
谢谢了。

[晓风残月0110]

create procedure Gettest
@UserID int
@ProductID int 
as
begin
select a.writetime,b.Area,b,Price into #
from t1 a left join t2 b on a.ProdeuctID = b.ProductPriceID
where UserID=@UserID  and  ProductID = @ProductID
declare @sql varchar(8000)
set @sql = 'select writetime'
select @sql = @sql + ',' + area+'=sum(case area when ''' + area+ ''' then price else 0 end )'
from # group by area order by area
set @sql = @sql + ' from # group by writetime'
end

[wxf87541163]

不好意思啊 不知道为什么查不出来啊。。这是表的数据，你能帮我试下么？

create table ProductPrice (ProductPriceID int,UserID int,ProductID int, WriteTime datetime)
insert into ProductPrice select 1,1,1,'2007-12-6'
insert into ProductPrice select 2,1,1,'2007-12-7'
insert into ProductPrice select 3,2,1,'2007-12-7'
insert into ProductPrice select 4,1,2,'2007-12-5'
create table ProductPriceInfo (ProductPriceID int,Area nvarchar(10),Price int)
insert into ProductPriceInfo select 1,'杭州',10
insert into ProductPriceInfo select 1,'上海',20
insert into ProductPriceInfo select 2,'杭州',40
insert into ProductPriceInfo select 2,'上海',20

[晓风残月0110]

create procedure Gettest
@UserID int,
@ProductID int 
as
begin
select a.WriteTime,b.Area,b.Price into #
from ProductPrice a left join ProductPriceInfo b on a.ProductID = b.ProductPriceID
where a.UserID=@UserID  and  a.ProductID = @ProductID
declare @sql varchar(8000)
set @sql = 'select WriteTime'
select @sql = @sql + ',' + Area+'=sum(case area when ''' + Area+ ''' then Price else 0 end )'
from # group by Area order by Area
set @sql = @sql + ' from # group by WriteTime'
exec(@sql)
end

go
--测试
exec Gettest 1,2

go
--删除测试
drop table ProductPrice
drop table ProductPriceInfo
drop proc Gettest
WriteTime               杭州          上海
----------------------- ----------- -----------
2007-12-05 00:00:00.000 40          20

[wxf87541163]

可以了！真的非常感谢！！