每个销售员每月一条销售业绩记录,在报表中希望看到每个销售员一年每个月的业绩,请问这个sql语句应该如何编写?
INSERT INTO #t('小李','201001',80)
INSERT INTO #t('小李','201002',110)
INSERT INTO #t('小李','201003',340)
INSERT INTO #t('小李','201004',400)
INSERT INTO #t('小李','201005',200)
INSERT INTO #t('小李','201006',600)INSERT INTO #t('小孙','201001',440)
INSERT INTO #t('小孙','201002',340)
INSERT INTO #t('小孙','201003',360)
INSERT INTO #t('小孙','201004',120)
INSERT INTO #t('小孙','201005',330)
INSERT INTO #t('小孙','201006',700)最终希望的到如下格式数据:
Name M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
小李 80 110 340 400 200 600
小孙 440 340 360 120 330 700
INSERT INTO #t('小李','201001',80)
INSERT INTO #t('小李','201002',110)
INSERT INTO #t('小李','201003',340)
INSERT INTO #t('小李','201004',400)
INSERT INTO #t('小李','201005',200)
INSERT INTO #t('小李','201006',600)INSERT INTO #t('小孙','201001',440)
INSERT INTO #t('小孙','201002',340)
INSERT INTO #t('小孙','201003',360)
INSERT INTO #t('小孙','201004',120)
INSERT INTO #t('小孙','201005',330)
INSERT INTO #t('小孙','201006',700)最终希望的到如下格式数据:
Name M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
小李 80 110 340 400 200 600
小孙 440 340 360 120 330 700
SELECT x.saleName,x.accountname,
sum(x.M01) AS M01, sum(x.M02) AS M02, sum(x.M03) AS M03,
sum(x.M04) AS M04, sum(x.M05) AS M05, sum(x.M06) AS M06,
sum(x.M07) AS M07, sum(x.M08) AS M08, sum(x.M09) AS M09,
sum(x.M10) AS M10, sum(x.M11) AS M11, sum(x.M12) AS M12
FROM (
SELECT saleName,accountname,
CASE substring(YearMonth,6,2) WHEN '01' THEN roomday ELSE 0 END AS M01,
CASE substring(YearMonth,6,2) WHEN '02' THEN roomday ELSE 0 END AS M02,
CASE substring(YearMonth,6,2) WHEN '03' THEN roomday ELSE 0 END AS M03,
CASE substring(YearMonth,6,2) WHEN '04' THEN roomday ELSE 0 END AS M04,
CASE substring(YearMonth,6,2) WHEN '05' THEN roomday ELSE 0 END AS M05,
CASE substring(YearMonth,6,2) WHEN '06' THEN roomday ELSE 0 END AS M06,
CASE substring(YearMonth,6,2) WHEN '07' THEN roomday ELSE 0 END AS M07,
CASE substring(YearMonth,6,2) WHEN '08' THEN roomday ELSE 0 END AS M08,
CASE substring(YearMonth,6,2) WHEN '09' THEN roomday ELSE 0 END AS M09,
CASE substring(YearMonth,6,2) WHEN '10' THEN roomday ELSE 0 END AS M10,
CASE substring(YearMonth,6,2) WHEN '11' THEN roomday ELSE 0 END AS M11,
CASE substring(YearMonth,6,2) WHEN '12' THEN roomday ELSE 0 END AS M12
FROM #t
) x
GROUP BY x.saleName,x.accountname
INSERT INTO #t values('小李','201001',80)
INSERT INTO #t values('小李','201002',110)
INSERT INTO #t values('小李','201003',340)
INSERT INTO #t values('小李','201004',400)
INSERT INTO #t values('小李','201005',200)
INSERT INTO #t values('小李','201006',600)INSERT INTO #t values('小孙','201001',440)
INSERT INTO #t values('小孙','201002',340)
INSERT INTO #t values('小孙','201003',360)
INSERT INTO #t values('小孙','201004',120)
INSERT INTO #t values('小孙','201005',330)
INSERT INTO #t values('小孙','201006',700)declare @sql varchar(max)
set @sql=''
select @sql=@sql+', M'+RIGHT(dt,2)+' = sum(case RIGHT(dt,2) when '''+RIGHT(dt,2)+''' then value else 0 end)'
from #t
group by RIGHT(dt,2)
exec('select name '+@sql+' from #t group by name')
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238
create table tbScore
(
ID int identity(1,1),
Name varchar(11),
No varchar(10),
score int
)INSERT INTO tbScore values ('小李','201001',80)
INSERT INTO tbScore values ('小李','201002',110)
INSERT INTO tbScore values ('小李','201003',340)
INSERT INTO tbScore values ('小李','201004',400)
INSERT INTO tbScore values ('小李','201005',200)
INSERT INTO tbScore values ('小李','201006',600)INSERT INTO tbScore values ('小孙','201001',440)
INSERT INTO tbScore values ('小孙','201002',340)
INSERT INTO tbScore values ('小孙','201003',360)
INSERT INTO tbScore values ('小孙','201004',120)
INSERT INTO tbScore values ('小孙','201005',330)
INSERT INTO tbScore values ('小孙','201006',700)
select Name,
Max(case substring(No,len(No)-1,2) when '01' then score else 0 end)[1月],
Max(case substring(No,len(No)-1,2) when '02' then score else 0 end)[2月],
Max(case substring(No,len(No)-1,2) when '03' then score else 0 end)[3月],
Max(case substring(No,len(No)-1,2) when '04' then score else 0 end)[4月],
Max(case substring(No,len(No)-1,2) when '05' then score else 0 end)[5月],
Max(case substring(No,len(No)-1,2) when '06' then score else 0 end)[6月],
Max(case substring(No,len(No)-1,2) when '07' then score else 0 end)[7月],
Max(case substring(No,len(No)-1,2) when '08' then score else 0 end)[8月],
Max(case substring(No,len(No)-1,2) when '09' then score else 0 end)[9月],
Max(case substring(No,len(No)-1,2) when '10' then score else 0 end)[10月],
Max(case substring(No,len(No)-1,2) when '11' then score else 0 end)[11月],
Max(case substring(No,len(No)-1,2) when '12' then score else 0 end)[12月]
from tbScore group by Name
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx