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

wxf87541163 2007-12-07 09:19:41

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

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

ID | UserID | Area
----|--------|-----
1 | 1 | 杭州
2 | 1 | 上海
3 | 2 | 武汉

UserID=1 and ProductID=2

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

...全文
132 6 打赏 收藏 举报

6 条回复

wxf87541163 2007-12-07

• 打赏
• 举报

``````
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 2007-12-07

``````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``````
• 打赏
• 举报

``````
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 2007-12-07

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

• 打赏
• 举报

``````
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
``````
• 打赏
• 举报

3.3w+

MS-SQL Server相关内容讨论专区

2007-12-07 09:19