求助行列转换后再求和的sql语句？？急用！！！

Question

  Tb__abc
  
  bigkind    amount_in      amount_out   department         dates   
   a001         5               8          S001      2008-07-10 12:23:57
   a001         4               6          S001      2008-07-11 13:15:34
   a002         5               8          S002      2008-07-10 12:23:57
   a002         10              12         S002      2008-07-10 16:12:23
   a002         5               5          S002      2008-07-10 21:23:44
   a003         3               6          S003      2008-07-10 12:23:57
   a005         5               8          S003      2008-07-10 12:23:57
   a005         5               7          S003      2008-07-10 22:57:12得到的样式为：
  deparment  a001In  a001Out  a002in  a002out a003in  a003out  a005in  a005out       dates
    s001       5       8                                                           2008-07-10 
    s001       4       6                                                           2008-07-11
    s002                       20      25                                          2008-07-10
    s003                                       3       6        10       15        2008-07-10行转列；把bigKind的列换成行名，统计出对应转换后列名跟department 日期下的 amount_in  和amount_Out总和

Answer 1

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

Answer 2

把dates用convert(varchar(10),dates,120)

Answer 3

if not object_id('Tempdb..#Tb__abc') is null
drop table #Tb__abc
Go
Create table #Tb__abc([bigkind] nvarchar(4),[amount_in] int,[amount_out] int,[department] nvarchar(4),[dates] Datetime)
Insert #Tb__abc
select N'a001',5,8,N'S001','2008-07-10 12:23:57' union all
select N'a001',4,6,N'S001','2008-07-11 13:15:34' union all
select N'a002',5,8,N'S002','2008-07-10 12:23:57' union all
select N'a002',10,12,N'S002','2008-07-10 16:12:23' union all
select N'a002',5,5,N'S002','2008-07-10 21:23:44' union all
select N'a003',3,6,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,8,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,7,N'S003','2008-07-10 22:57:12'
Godeclare @i int,@s nvarchar(4000)
select top 1 @i=count(*),@s='' from #Tb__abc group by convert(varchar(10),[dates],120),[bigkind] order by count(*) desc
while @i>0
select @s=',[amount_in'+rtrim(@i)+']=sum(case when con='+rtrim(@i)+' then [amount_in] else 0 end),'+
  '[amount_out'+rtrim(@i)+']=sum(case when con='+rtrim(@i)+' then [amount_out] else 0 end)'+@s,@i=@i-1
exec('select [bigkind]'+@s+',convert(varchar(10),[dates],120) dates
from 
(Select *,con=(select count(1) from #Tb__abc where [bigkind]=a.[bigkind] and datediff(d,[dates],a.[dates])=0 and [dates]<=a.[dates]) from #Tb__abc a)T
group by [bigkind],convert(varchar(10),[dates],120)')
select
[bigkind],
[amount_in1]=sum(case when con=1 then [amount_in] else 0 end),
[amount_out1]=sum(case when con=1 then [amount_out] else 0 end),
[amount_in2]=sum(case when con=2 then [amount_in] else 0 end),
[amount_out2]=sum(case when con=2 then [amount_out] else 0 end),
[amount_in3]=sum(case when con=3 then [amount_in] else 0 end),
[amount_out3]=sum(case when con=3 then [amount_out] else 0 end),
convert(varchar(10),[dates],120) from 
(Select *,con=(select count(1) from #Tb__abc where [bigkind]=a.[bigkind] and datediff(d,[dates],a.[dates])=0 and [dates]<=a.[dates]) from #Tb__abc a)
T
group by [bigkind],convert(varchar(10),[dates],120)
/*
bigkind amount_in1  amount_out1 amount_in2  amount_out2 amount_in3  amount_out3 dates
------- ----------- ----------- ----------- ----------- ----------- ----------- ----------
a001    5           8           0           0           0           0           2008-07-10
a002    5           8           10          12          5           5           2008-07-10
a003    3           6           0           0           0           0           2008-07-10
a005    5           8           5           7           0           0           2008-07-10
a001    4           6           0           0           0           0           2008-07-11(5 個資料列受到影響)
*/

Answer 4

if not object_id('Tempdb..#Tb__abc') is null
drop table #Tb__abc
Go
Create table #Tb__abc([bigkind] nvarchar(4),[amount_in] int,[amount_out] int,[department] nvarchar(4),[dates] Datetime)
Insert #Tb__abc
select N'a001',5,8,N'S001','2008-07-10 12:23:57' union all
select N'a001',4,6,N'S001','2008-07-11 13:15:34' union all
select N'a002',5,8,N'S002','2008-07-10 12:23:57' union all
select N'a002',10,12,N'S002','2008-07-10 16:12:23' union all
select N'a002',5,5,N'S002','2008-07-10 21:23:44' union all
select N'a003',3,6,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,8,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,7,N'S003','2008-07-10 22:57:12'
Godeclare @s nvarchar(4000)
set @s=''
select 
@s=@s+',['+[department]+'_in]=isnull(rtrim(sum(case when [department]='+quotename([department],'''')+' then [amount_in] end)),''''),'+
    '['+[department]+'_out]=isnull(rtrim(sum(case when [department]='+quotename([department],'''')+' then [amount_in] end)),'''')'
from 
#Tb__abc
group by [department]exec('select [bigkind]'+@s+',convert(varchar(10),[dates],120) dates
from #Tb__abc 
group by [bigkind],convert(varchar(10),[dates],120)')
--靜態:
select [bigkind],[S001_in]=isnull(rtrim(sum(case when [department]='S001' then [amount_in] end)),''),[S001_out]=isnull(rtrim(sum(case when [department]='S001' then [amount_in] end)),''),[S002_in]=isnull(rtrim(sum(case when [department]='S002' then [amount_in] end)),''),[S002_out]=isnull(rtrim(sum(case when [department]='S002' then [amount_in] end)),''),[S003_in]=isnull(rtrim(sum(case when [department]='S003' then [amount_in] end)),''),[S003_out]=isnull(rtrim(sum(case when [department]='S003' then [amount_in] end)),''),convert(varchar(10),[dates],120) dates
from #Tb__abc 
group by [bigkind],convert(varchar(10),[dates],120)/*
bigkind S001_in      S001_out     S002_in      S002_out     S003_in      S003_out     dates
------- ------------ ------------ ------------ ------------ ------------ ------------ ----------
a001    5            5                                                                2008-07-10
a002                              20           20                                     2008-07-10
a003                                                        3            3            2008-07-10
a005                                                        10           10           2008-07-10
a001    4            4                                                                2008-07-11
(5 個資料列受到影響)*/

Answer 5

bigkind要变为department吗？--靜態:
select [department],[S001_in]=isnull(rtrim(sum(case when [department]='S001' then [amount_in] end)),''),[S001_out]=isnull(rtrim(sum(case when [department]='S001' then [amount_out] end)),''),[S002_in]=isnull(rtrim(sum(case when [department]='S002' then [amount_in] end)),''),[S002_out]=isnull(rtrim(sum(case when [department]='S002' then [amount_out] end)),''),[S003_in]=isnull(rtrim(sum(case when [department]='S003' then [amount_in] end)),''),[S003_out]=isnull(rtrim(sum(case when [department]='S003' then [amount_out] end)),''),convert(varchar(10),[dates],120) dates
from #Tb__abc 
group by [department],convert(varchar(10),[dates],120)

Answer 6

  deparment  a001In  a001Out  a002in  a002out a003in  a003out  a005in  a005out      dates
    s001      5      8                                                          2008-07-10
    s001      4      6                                                          2008-07-11
    s002                      20      25                                          2008-07-10
    s003                                      3      6        10      15        2008-07-10
if not object_id('Tempdb..#Tb__abc') is null
    drop table #Tb__abc
Go
Create table #Tb__abc([bigkind] nvarchar(4),[amount_in] int,[amount_out] int,[department] nvarchar(4),[dates] Datetime)
Insert #Tb__abc
select N'a001',5,8,N'S001','2008-07-10 12:23:57' union all
select N'a001',4,6,N'S001','2008-07-11 13:15:34' union all
select N'a002',5,8,N'S002','2008-07-10 12:23:57' union all
select N'a002',10,12,N'S002','2008-07-10 16:12:23' union all
select N'a002',5,5,N'S002','2008-07-10 21:23:44' union all
select N'a003',3,6,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,8,N'S003','2008-07-10 12:23:57' union all
select N'a005',5,7,N'S003','2008-07-10 22:57:12'
Godeclare @s nvarchar(4000)
set @s=''
select 
    @s=@s+',['+[department]+'_in]=isnull(rtrim(sum(case when [department]='+quotename([department],'''')+' then [amount_in] end)),''''),'+
        '['+[department]+'_out]=isnull(rtrim(sum(case when [department]='+quotename([department],'''')+' then [amount_out] end)),'''')'--這里改改
from 
    #Tb__abc
group by [department]exec('select [department]'+@s+',convert(varchar(10),[dates],120) dates
from #Tb__abc 
group by [department],convert(varchar(10),[dates],120) order by [department]')select [department],[S001_in]=isnull(rtrim(sum(case when [department]='S001' then [amount_in] end)),''),[S001_out]=isnull(rtrim(sum(case when [department]='S001' then [amount_out] end)),''),[S002_in]=isnull(rtrim(sum(case when [department]='S002' then [amount_in] end)),''),[S002_out]=isnull(rtrim(sum(case when [department]='S002' then [amount_out] end)),''),[S003_in]=isnull(rtrim(sum(case when [department]='S003' then [amount_in] end)),''),[S003_out]=isnull(rtrim(sum(case when [department]='S003' then [amount_out] end)),''),convert(varchar(10),[dates],120) dates
from #Tb__abc 
group by [department],convert(varchar(10),[dates],120) order by [department]
/*
department S001_in      S001_out     S002_in      S002_out     S003_in      S003_out     dates
---------- ------------ ------------ ------------ ------------ ------------ ------------ ----------
S001       5            8                                                                2008-07-10
S001       4            6                                                                2008-07-11
S002                                 20           25                                     2008-07-10
S003                                                           13           21           2008-07-10(4 個資料列受到影響)
*/

Answer 7

简单了,没上面写的好,
IF not object_id('Tb') is null
drop table Tb
Go
Create table Tb(bigkind varchar(5),amount_in int,amount_out int,department varchar(5),
dates smalldatetime)
Insert Tb
Select 'a001',5,8,'S001','2008-07-10 12:23:57' union all
Select 'a001',4,6,'S001','2008-07-11 13:15:34' union all
Select 'a002',5,8,'S002','2008-07-10 12:23:57' union all 
Select 'a002',10,12,'S002','2008-07-10 16:12:23' union all 
Select 'a002',5,5,'S002','2008-07-10 21:23:44' union all 
Select 'a003',3,6,'S003','2008-07-10 12:23:57' union all 
Select 'a005',5,8,'S003','2008-07-10 12:23:57' union all 
Select 'a005',5,7,'S003','2008-07-10 22:57:12'
Select * from Tbdeclare @sql varchar(7000)
set @sql='Select Distinct department'
Select @sql=@sql+',['+bigkind+']=SUM(case bigkind when '''+bigkind+''' then amount_in else 0 end)'
+',['+bigkind+']=Sum(case bigkind when '''+bigkind+''' then amount_out else 0 end)'
from (select distinct bigkind from Tb)aexec(@sql+'from Tb group by department,convert(varchar(10),dates,120)')