declare @sql varchar(8000)
set @sql = 'select drivername as 姓名'
select @sql = @sql + ', max(case convert(varchar, moneydate,120) when ''' + moneydate + ''' then totalmoney else 0 end) [' + moneydate + ']'
from (select distinct convert(varchar, moneydate, 120) moneydate from moneylist) as a
set @sql = @sql + ' from moneylist group by drivername'
exec(@sql)
set @sql = 'select drivername as 姓名'
select @sql = @sql + ', max(case convert(varchar, moneydate,120) when ''' + moneydate + ''' then totalmoney else 0 end) [' + moneydate + ']'
from (select distinct convert(varchar, moneydate, 120) moneydate from moneylist) as a
set @sql = @sql + ' from moneylist group by drivername'
exec(@sql)
drivername varchar
moneydate dateTime (帶有時間)
declare @sql varchar(8000)
set @sql = 'select drivername as ' + '姓名'
select @sql = @sql + ', max(case convert(varchar, moneydate,120) when ''' + dd + ''' then totalmoney else 0 end) [' + dd + ']'
from (select distinct convert(varchar, moneydate, 120) as dd from moneylist) as a
set @sql = @sql + ' from moneylist group by drivername'
exec(@sql)
insert into tb values('aa' , '2008-01-01 00:01:01' , 1)
insert into tb values('aa' , '2008-01-02 00:01:01' , 2)
insert into tb values('aa' , '2008-01-03 00:01:01' , 3)
insert into tb values('aa' , '2008-01-04 00:01:01' , 4)
insert into tb values('aa' , '2008-01-05 00:01:01' , 5)
insert into tb values('bb' , '2008-01-01 00:01:01' , 6)
insert into tb values('bb' , '2008-01-02 00:02:01' , 7)
godeclare @sql varchar(8000)
set @sql = 'select drivername as 姓名'
select @sql = @sql + ', max(case convert(varchar(10), moneydate,120) when ''' + convert(varchar(10), moneydate,120) + ''' then totalmoney else '''' end) [' + convert(varchar(10), moneydate,120) + ']'
from (select distinct convert(varchar(10), moneydate, 120) moneydate from tb) as a
set @sql = @sql + ' from tb group by drivername'
exec(@sql) drop table tb/*
姓名 2008-01-01 2008-01-02 2008-01-03 2008-01-04 2008-01-05
---------- ----------- ----------- ----------- ----------- -----------
aa 1 2 3 4 5
bb 6 7 0 0 0
*/
set @sql = 'select drivername as 姓名'
select @sql = @sql + ', max(case convert(varchar(10), moneydate,120) when ''' + convert(varchar(10), moneydate,120) + ''' then totalmoney else '''' end) [' + convert(varchar(10), moneydate,120) + ']'
from (select distinct convert(varchar(10), moneydate, 120) moneydate from tb) as a where moneydate between '2008-01-01' and '2008-01-03'
set @sql = @sql + ' from tb group by drivername'
exec(@sql)
001 A 5 100 2008-01-01
002 B 3 50 2008-01-01
003 A 10 80 2008-01-03
004 C 5 6 2008-01-05
005 D 2 50 2008-01-01需要的格式
-------------------------------------------------
姓名 2008-01-01 2008-01-02 2008-01-03
--------------------------------------------------
A QTY 5 100 10
MONEY 100 0 80