在一个视图v_basevoyage中有这样的字段:
select portfuname,tt from v_basevoyage
services portfuname tt
c1 HongKong 0
c1 他科马 12
c1 奥克兰 15
c1 檀香山 20
c1 关岛 30
c2 盐田 12
c2 顺 4
怎样得到:
services days 他科马 奥克兰 檀香山 关岛
c1 HongKong 12 15 20 30
我写了N次了,老是说字段名无效,贵请各位大哥帮忙.
------解决方案--------------------
Select
services,
Max(Case portfuname When 'HongKong ' Then tt Else 0 End) As HongKong,
Max(Case portfuname When '他科马 ' Then tt Else 0 End) As 他科马,
Max(Case portfuname When '奥克兰 ' Then tt Else 0 End) As 奥克兰,
Max(Case portfuname When '檀香山 ' Then tt Else 0 End) As 檀香山,
Max(Case portfuname When '关岛 ' Then tt Else 0 End) As 关岛
From
v_basevoyage
Where
services = 'c1 '
Group By
services
------解决方案--------------------
create table v_basevoyage(services varchar(20),portfuname varchar(20),tt int)
insert into v_basevoyage select 'c1 ', 'HongKong ',0
union select 'c1 ', '他科马 ',12
union select 'c1 ', '奥克兰 ',5
union select 'c1 ', '檀香山 ',20
union select 'c1 ', '关岛 ',30
union select 'c2 ', '盐田 ',12
union select 'c2 ', '顺 ',4
go
create procedure q
@s varchar(20)---服务线路名称
as
begin
declare @sql varchar(4000)--查询语句
declare @port varchar(20)
set @sql=N 'select services, '
select @port=portfuname from v_basevoyage where [email protected] and tt=0
if @port is null
return---没有始发港
set @[email protected]+ ' ' ' '[email protected]+ ' ' ' days '
select TOP 100 Percent portfuname into #t from v_basevoyage where [email protected] Group By portfuname,TT HAVING TT> 0 order by TT asc
select @[email protected] +N ',sum(case portfuname when ' ' '+portfuname + ' ' ' then tt end )[ ' +portfuname + '] '
from #t
select @[email protected] + N ' from v_basevoyage group by services HAVing services= ' ' '[email protected]+ ' ' ' '
exec (@sql)
drop table #t
end
go
exec q 'C2 '
drop procedure q