求助！SQL 如何写语句 按客户分类，统计每年的所有金额！麻烦帮忙写一下，急！在线等

[ade-]

本人初学者，请好心人指点！！
SQL 如何写语句 按客户分类，统计每年的所有金额！ 如下图描述

[qq_17482963]

数据和字段自己替换一下

[qq_17482963]

 测试数据
select * into #t from (
    SELECT 'A' as name,'20110505' as pdate,5 as data UNION ALL
    SELECT 'A','20110606',10 UNION ALL
    SELECT 'B','20120103',8 UNION ALL
    SELECT 'C','20130605',18) a

declare @c nvarchar(500)
declare @s nvarchar(max)


select @c=ISNULL(+@c+',','')+pdate from (select distinct '['+convert(nvarchar(10),YEAR(pdate))+']'pdate from #t) a
print @c
set @s='
select * from (
        select name,YEAR(pdate)pdate,data from #t
        ) p pivot (sum(data) for pdate in ('+@c+'))t'
        print @s
exec (@s)

[ade-]

引用 1 楼 qq_17482963 的回复:

 测试数据
select * into #t from (
    SELECT 'A' as name,'20110505' as pdate,5 as data UNION ALL
    SELECT 'A','20110606',10 UNION ALL
    SELECT 'B','20120103',8 UNION ALL
    SELECT 'C','20130605',18) a

declare @c nvarchar(500)
declare @s nvarchar(max)


select @c=ISNULL(+@c+',','')+pdate from (select distinct '['+convert(nvarchar(10),YEAR(pdate))+']'pdate from #t) a
print @c
set @s='
select * from (
        select name,YEAR(pdate)pdate,data from #t
        ) p pivot (sum(data) for pdate in ('+@c+'))t'
        print @s
exec (@s)

谢谢了~已经解决 ！ select 客户名称, sum(case left(交易日期,4) when '2010' then 金额 else 0 end) "2010年"， sum(case left(交易日期,4) when '2011' then 金额 else 0 end) "2011年"， sum(case left(交易日期,4) when '2012' then 金额 else 0 end) "2012年"， sum(case left(交易日期,4) when '2013' then 金额 else 0 end) "2013年" from [table] group by 客户名称