中秋快到了,求SQL语句.
有这样的数据:
Services Vessel POL SI CY ETD
b2 GFSH Sh 09-04 09-04 09-07
b2 GFSH HK 09-09 09-10 09-11
b2 SZEH Sh 09-04 09-04 09-07
b2 SZEH HK 09-09 09-10 09-11
c1 XFYH HK 08-31 09-01 09-02
c1 XFYH Ka 09-01 09-04 09-05
m2 GFSH Sh 08-30 09-01 09-03
m2 GFSH NB 09-01 09-03 09-05
..
..
在得到这样的结果:
Services Vessel Sh(SI) Sh(CY) Sh(ETD) HK(SI) HK(CY) HK(ETD) ....
b2 GFSH 09-04 09-04 09-07 09-09 09-10 09-11 .....
b2 SZEH 09-04 09-04 09-07 09-09 09-10 09-11
Services Vessel HK(SI) HK(CY) HK(ETD) Ka(SI) Ka(CY) Ka(ETD) ......
c1 XFYH 08-31 09-01 09-02 09-01 09-04 09-05
...
....
了即以Services和 Vessel 分组,然后把POL横排
[解决办法]
create table xyza(Services varchar(10),Vessel varchar(10),POL varchar(10),SI varchar(10),CY varchar(10),ETD varchar(10))
insert xyza select 'b2 ', 'GFSH ', 'Sh ' , '09-04 ', '09-04 ', '09-07 '
union all select 'b2 ' , 'GFSH ' , 'HK ' , '09-09 ' , '09-10 ' , '09-11 '
union all select 'b2 ' , 'SZEH ' , 'Sh ' , '09-04 ' , '09-04 ' , '09-07 '
union all select 'b2 ' , 'SZEH ' , 'HK ' , '09-09 ' , '09-10 ' , '09-11 '
union all select 'c1 ' , 'XFYH ' , 'HK ' , '08-31 ' , '09-01 ' , '09-02 '
union all select 'c1 ' , 'XFYH ' , 'Ka ' , '09-01 ' , '09-04 ' , '09-05 '
union all select 'm2 ' , 'GFSH ' , 'Sh ' , '08-30 ' , '09-01 ' , '09-03 '
union all select 'm2 ' , 'GFSH ' , 'NB ' , '09-01 ' , '09-03 ' , '09-05 '
declare @a varchar(8000)
select @a=isnull(@a+ ', ', ' ')+ ' min(case when pol= ' ' '+pol+ ' ' ' then si end) [ '+pol+ '(SI)],min(case when pol= ' ' '+pol+ ' ' ' then cy end) [ '+pol+ '(CY)], min(case when pol= ' ' '+pol+ ' ' ' then etd end) [ '+pol+ '(ETD)] '
from xyza group by pol
select @a= 'select services,vessel, '+@a+ ' from xyza group by services,vessel order by services,vessel '
exec(@a)
[解决办法]
create table tb(Services varchar(10),Vessel varchar(10),POL varchar(10),SI varchar(10),CY varchar(10),ETD varchar(10))
insert into tb values( 'b2 ', 'GFSH ', 'Sh ', '09-04 ', '09-04 ', '09-07 ')
insert into tb values( 'b2 ', 'GFSH ', 'HK ', '09-09 ', '09-10 ', '09-11 ')
insert into tb values( 'b2 ', 'SZEH ', 'Sh ', '09-04 ', '09-04 ', '09-07 ')
insert into tb values( 'b2 ', 'SZEH ', 'HK ', '09-09 ', '09-10 ', '09-11 ')
insert into tb values( 'c1 ', 'XFYH ', 'HK ', '08-31 ', '09-01 ', '09-02 ')
insert into tb values( 'c1 ', 'XFYH ', 'Ka ', '09-01 ', '09-04 ', '09-05 ')
insert into tb values( 'm2 ', 'GFSH ', 'Sh ', '08-30 ', '09-01 ', '09-03 ')
insert into tb values( 'm2 ', 'GFSH ', 'NB ', '09-01 ', '09-03 ', '09-05 ')
go
declare @sql varchar(8000)
set @sql = 'select Services,Vessel '
select @sql = @sql + ' , max(case POL when ' ' ' + POL + ' ' ' then SI end) [ ' + POL + '(SI)] '
+ ' , max(case POL when ' ' ' + POL + ' ' ' then CY end) [ ' + POL + '(CY)] '
+ ' , max(case POL when ' ' ' + POL + ' ' ' then ETD end) [ ' + POL + '(ETD)] '
from (select distinct POL from tb) as a
set @sql = @sql + ' from tb group by Services,Vessel '
exec(@sql)
drop table tb
/*
Services Vessel HK(SI) HK(CY) HK(ETD) Ka(SI) Ka(CY) Ka(ETD) NB(SI) NB(CY) NB(ETD) Sh(SI) Sh(CY) Sh(ETD)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
b2 GFSH 09-09 09-10 09-11 NULL NULL NULL NULL NULL NULL 09-04 09-04 09-07
m2 GFSH NULL NULL NULL NULL NULL NULL 09-01 09-03 09-05 08-30 09-01 09-03
b2 SZEH 09-09 09-10 09-11 NULL NULL NULL NULL NULL NULL 09-04 09-04 09-07
c1 XFYH 08-31 09-01 09-02 09-01 09-04 09-05 NULL NULL NULL NULL NULL NULL
*/
------解决方案--------------------
Declare @sql Varchar(8000)
Select @sql=Coalesce(@sql+ ', ', ' ')+ 'Max(Case When Pol= ' ' '+Pol+ ' ' ' Then SI Else ' ' ' ' End) As [ '+POL+ '(SI)],
Max(Case When Pol= ' ' '+Pol+ ' ' ' Then CY Else ' ' ' ' End) As [ '+POL+ '(CY)],
Max(Case When Pol= ' ' '+Pol+ ' ' ' Then ETD Else ' ' ' ' End) As [ '+POL+ '(ETD)] '
From TableName Group By Pol
Exec( 'Select Services,Vessel, '+@sql+ ' From TableName Group By Services,Vessel Order By Services,Vessel ')
[解决办法]
create table aaa(Services varchar(10),Vessel varchar(10),POL varchar(10),SI varchar(10),CY varchar(10),ETD varchar(10))
insert into aaa values( 'b2 ', 'GFSH ', 'Sh ', '09-04 ', '09-04 ', '09-07 ')
insert into aaa values( 'b2 ', 'GFSH ', 'HK ', '09-09 ', '09-10 ', '09-11 ')
insert into aaa values( 'b2 ', 'SZEH ', 'Sh ', '09-04 ', '09-04 ', '09-07 ')
insert into aaa values( 'b2 ', 'SZEH ', 'HK ', '09-09 ', '09-10 ', '09-11 ')
insert into aaa values( 'c1 ', 'XFYH ', 'HK ', '08-31 ', '09-01 ', '09-02 ')
insert into aaa values( 'c1 ', 'XFYH ', 'Ka ', '09-01 ', '09-04 ', '09-05 ')
insert into aaa values( 'm2 ', 'GFSH ', 'Sh ', '08-30 ', '09-01 ', '09-03 ')
insert into aaa values( 'm2 ', 'GFSH ', 'NB ', '09-01 ', '09-03 ', '09-05 ')
go
Declare @sql varchar(5000)
set @sql= 'select Services,vessel '
select @sql=@sql+ ',max(case when Pol= ' ' '+Pol+ ' ' ' then si else null end) as [ '+pol+ '(si)] ' +
',max(case when Pol= ' ' '+Pol+ ' ' ' then cy else null end) as [ '+pol+ '(cy)] ' +
',max(case when Pol= ' ' '+Pol+ ' ' ' then etd else null end) as [ '+pol+ '(etd)] '
from (select distinct pol from aaa )a
set @sql=@sql+ ' from aaa group by Services,vessel '
exec( @sql)
drop table aaa
------------------------------------------------
Services vessel HK(si) HK(cy) HK(etd) Ka(si) Ka(cy) Ka(etd) NB(si) NB(cy) NB(etd) Sh(si) Sh(cy) Sh(etd)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
b2 GFSH 09-09 09-10 09-11 NULL NULL NULL NULL NULL NULL 09-04 09-04 09-07
m2 GFSH NULL NULL NULL NULL NULL NULL 09-01 09-03 09-05 08-30 09-01 09-03
b2 SZEH 09-09 09-10 09-11 NULL NULL NULL NULL NULL NULL 09-04 09-04 09-07
c1 XFYH 08-31 09-01 09-02 09-01 09-04 09-05 NULL NULL NULL NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。
(4 行受影响)
