22,209
社区成员
发帖
与我相关
我的任务
分享
名称 号码 CD 日期 金额
a 1 A 1991-01-01 500
a 1 C 1992-01-01 0
a 3 C 1993-01-01 0
a 1 A 1994-01-01 400
a 1 A 1995-01-01 300
a 2 B 1996-01-01 0
b 1 A 1997-01-01 0
b 1 B 1998-01-01 0
b 1 C 1999-01-01 0
b 2 A 2001-01-01 0
b 2 B 2002-01-01 500
b 2 B 2003-01-01 500
b 2 C 2004-01-01 0
b 3 A 2005-01-01 0
b 3 B 2006-01-01 0
.. .. .. ........... ..
名称 号码 CD 1991-01-01 1992-01-01 1993-01-01 1994-01-01 1995-01-01 .........
a 1 A 500
a 1 C
a 2 A 400 300
a 2 B
a 3 C
b 1 A
b 1 B
b 1 C
b 2 A
b 2 B
b 2 C 500
b 3 A
b 3 B
[/align][/align]--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
DECLARE @sql NVARCHAR(MAX)='select?名称,号码,CD?,
?sum(case?日期?when?''1991-01-01''?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?''1992-01-01''?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?''1993-01-01''?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?''1994-01-01''?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?''1995-01-01''?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?''1996-01-01''?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?''1997-01-01''?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?''1998-01-01''?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?''1999-01-01''?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?''2001-01-01''?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?''2002-01-01''?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?''2003-01-01''?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?''2004-01-01''?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?''2005-01-01''?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?''2006-01-01''?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
EXEC(@sql)
(15 行受影响)
select?名称,号码,CD?,
?sum(case?日期?when?'1991-01-01'?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?'1992-01-01'?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?'1993-01-01'?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?'1994-01-01'?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?'1995-01-01'?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?'1996-01-01'?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?'1997-01-01'?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?'1998-01-01'?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?'1999-01-01'?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?'2001-01-01'?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?'2002-01-01'?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?'2003-01-01'?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?'2004-01-01'?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?'2005-01-01'?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?'2006-01-01'?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
EXEC(@sql)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
PRINT(@sql)
SELECT 名称 ,
号码 ,
CD ,
SUM(CASE 日期
WHEN '1991-01-01' THEN 金额
ELSE 0
END) [1991-01-01] ,
SUM(CASE 日期
WHEN '1992-01-01' THEN 金额
ELSE 0
END) [1992-01-01] ,
SUM(CASE 日期
WHEN '1993-01-01' THEN 金额
ELSE 0
END) [1993-01-01] ,
SUM(CASE 日期
WHEN '1994-01-01' THEN 金额
ELSE 0
END) [1994-01-01] ,
SUM(CASE 日期
WHEN '1995-01-01' THEN 金额
ELSE 0
END) [1995-01-01] ,
SUM(CASE 日期
WHEN '1996-01-01' THEN 金额
ELSE 0
END) [1996-01-01] ,
SUM(CASE 日期
WHEN '1997-01-01' THEN 金额
ELSE 0
END) [1997-01-01] ,
SUM(CASE 日期
WHEN '1998-01-01' THEN 金额
ELSE 0
END) [1998-01-01] ,
SUM(CASE 日期
WHEN '1999-01-01' THEN 金额
ELSE 0
END) [1999-01-01] ,
SUM(CASE 日期
WHEN '2001-01-01' THEN 金额
ELSE 0
END) [2001-01-01] ,
SUM(CASE 日期
WHEN '2002-01-01' THEN 金额
ELSE 0
END) [2002-01-01] ,
SUM(CASE 日期
WHEN '2003-01-01' THEN 金额
ELSE 0
END) [2003-01-01] ,
SUM(CASE 日期
WHEN '2004-01-01' THEN 金额
ELSE 0
END) [2004-01-01] ,
SUM(CASE 日期
WHEN '2005-01-01' THEN 金额
ELSE 0
END) [2005-01-01] ,
SUM(CASE 日期
WHEN '2006-01-01' THEN 金额
ELSE 0
END) [2006-01-01]
FROM #T
GROUP BY 名称 ,
号码 ,
CD
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
exec(@sql)
[/quote]
最上边呢?看着也没输入?,怎么会提示这个呢