34,576
社区成员
发帖
与我相关
我的任务
分享
--动态语句解决方案
declare @sql varchar(8000),@sql2 varchar(8000)
set @sql = ''
select @sql = @sql + 'SUM(CASE WHEN 缴费年度 = '''+ 缴费年度 +''' THEN 金额 END) AS [年费'+缴费年度+'], '
from (
select distinct 缴费年度 from #tb)a
set @sql = left(@sql,len(@sql)-1)
set @sql2 = 'select *
from #ta a,(SELECT 信息编号,'+@sql+'
FROM #tb
GROUP BY 信息编号) b where a.序号 = b.信息编号;'
--print @sql2
exec(@sql2)
--构造测试数据
create table #ta(序号 int, 专利名称 varchar(50), 专利类型 varchar(50), 申请费用 money, 专利权人 varchar(10)
,申请号 varchar(50),公开号 varchar(50),代理结构 varchar(50),申请日期 datetime, 公开日期 datetime
, 专利摘要 varchar(50), 申请时间 varchar(10), 状态 varchar(10), 备注 varchar(255))
insert into #ta
select 1,'一种床架接头','实用新型',null,'aa','20060101001','CN20090509','aaaa','20060901','20070101','摘要1','2007','取消','' union all
select 1,'一种新型床罩','实用新型',null,'bb','20060101002','CN20090501','bbbb','20060901','20070101','摘要1','2007','取消','' union all
select 1,'一种新型组合折叠','实用新型',null,'cc','20060101003','CN20090502','cccc','20060901','20070101','摘要1','2007','正常',''
create table #tb (缴费编号 varchar(10),信息编号 int, 缴费年度 varchar(10),金额 money, 备注 varchar(255))
insert into #tb
select 'T1',1,'2009',195,'' union all
select 'T2',2,'2010',960,'' union all
select 'T3',3,'2010',960,'' union all
select 'T4',3,'2011',1260,'' union all
select 'T5',3,'2012',1260,'' union all
select 'T6',2,'2009',160,''
--解决方案
select *
from #ta a,(SELECT 信息编号,
SUM(CASE WHEN 缴费年度 = '2009' THEN 金额 END) AS [年费2009],
SUM(CASE WHEN 缴费年度 = '2010' THEN 金额 END) AS [年费2010],
SUM(CASE WHEN 缴费年度 = '2011' THEN 金额 END) AS [年费2011],
SUM(CASE WHEN 缴费年度 = '2012' THEN 金额 END) AS [年费2012]
FROM #tb
GROUP BY 信息编号) b where a.序号 = b.信息编号;
GO
SELECT custid,
SUM(CASE WHEN orderyear = 2002 THEN qty END) AS [2002],
SUM(CASE WHEN orderyear = 2003 THEN qty END) AS [2003],
SUM(CASE WHEN orderyear = 2004 THEN qty END) AS [2004]
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
GROUP BY custid;
GO