再求个sql语句？？

Question

    Reg       big       Amount         Dates
   a005       a001       20.00        2008-07-06 12:35:11
   a005       a001       100.00       2008-07-06 17:25:56
   a005       a001       25.00        2008-07-06 12:56:38
   a005       a002       11.00        2008-07-06 12:47:06
   a006       a003       20.00        2008-07-06 15:12:09
   a005       a002       5.00         2008-07-07 12:23:32
   a007       a003       8.00         2008-07-07 17:45:12得到的形式为：
  Reg      a001      a002      a003          Dates
  a005    145.00     11.00                 2008-07-06  
  a006                        20.00        2008-07-06
  a005              5.00                   2008-07-07 
  a007                        8.00         2008-07-07是把big转换成列名，并且根据Reg 和big名来统计Amount  

Answer 1

行列互转http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 2

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Reg] nvarchar(4),[big] nvarchar(4),[Amount] decimal(18,2),[Dates] Datetime)
Insert #T
select N'a005',N'a001',20.00,'2008-07-06 12:35:11' union all
select N'a005',N'a001',100.00,'2008-07-06 17:25:56' union all
select N'a005',N'a001',25.00,'2008-07-06 12:56:38' union all
select N'a005',N'a002',11.00,'2008-07-06 12:47:06' union all
select N'a006',N'a003',20.00,'2008-07-06 15:12:09' union all
select N'a005',N'a002',5.00,'2008-07-07 12:23:32' union all
select N'a007',N'a003',8.00,'2008-07-07 17:45:12'
Go
select [Reg] ,[a001]=isnull(rtrim([a001]),''),[a002]=isnull(rtrim([a002]),''),[a003]=isnull(rtrim([a003]),''),[Dates]
from (Select [Reg] ,[big],[Amount],[Dates]=convert(varchar(10),[Dates],120) from #T)a
pivot
(sum([Amount]) for [big] in([a001],[a002],[a003]))bReg  a001                                      a002                                      a003                                      Dates
---- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------
a005 145.00                                    11.00                                                                               2008-07-06
a006                                                                                     20.00                                     2008-07-06
a005                                           5.00                                                                                2008-07-07
a007                                                                                     8.00                                      2008-07-07(4 個資料列受到影響)

Answer 3

select [Reg] ,[a001]=isnull(rtrim([a001]),''),[a002]=isnull(rtrim([a002]),''),[a003]=isnull(rtrim([a003]),''),[Dates]
from (Select [Reg] ,[big],[Amount],[Dates]=convert(varchar(10),[Dates],120) from #T)a
pivot   -------这个是什么意思 ，？？？在这里报错？？？
(sum([Amount]) for [big] in([a001],[a002],[a003]))b

Answer 4

行列互转 http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 5

create table u_db(Reg varchar(10),      big varchar(10),     Amount numeric(5,2)  ,    Dates datetime)
go
insert u_db
select  'a005',      'a001',      20.00       ,'2008-07-06 12:35:11' 
union all select  'a005',      'a001',      100.00      ,'2008-07-06 17:25:56' 
union all select  'a005',      'a001',      25.00       ,'2008-07-06 12:56:38' 
union all select  'a005',      'a002',      11.00       ,'2008-07-06 12:47:06' 
union all select  'a006',      'a003',      20.00       ,'2008-07-06 15:12:09' 
union all select  'a005',      'a002',      5.00        ,'2008-07-07 12:23:32' 
union all select  'a007',      'a003',      8.00        ,'2008-07-07 17:45:12' select * from u_dbselect Reg,convert(varchar(10),Dates,120)  from U_db group by Reg,convert(varchar(10),Dates,120) select * from u_dbdeclare @str varchar(1000)
select @str='select Reg'
select @str=@str+','+quotename(big)+N'=sum(case big when '+quotename(big,'''')+N' then Amount else 0 end)'from u_db group by big 
print @strexec (@str+N' ,convert(varchar(10),Dates,120) as Dates from u_db group by Reg,convert(varchar(10),Dates,120)')