/*
belongto :业务员编号
bemonth :月份
goodsqty :销售数量
moneytotalrmb:销售金额要转换成如下格式
业务员 月份1数量、月份1金额、月份2数量、月份2金额……月份12金额
现在问题是并不一定是1-12月份都有,也许只有1-5月份的数据,但仍要求格式如上。
数据是由出货表生成的临时表,没有主键。
*/
--以下为测试环境,谢谢各位.
if exists (select * from sysobjects where id = object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST]create table test
(belongto char(8),
bemonth char(2),
goodsqty float,
moneytotalrmb money)insert test(belongto,bemonth,goodsqty,moneytotalrmb)
select 'hlt0026','01',14312,455.23
union all select 'hlt0001','01',18767,2445.2
union all select 'hlt0015','08',14567,265.5
union all select 'hlt0009','09',18600,2445.1
union all select 'hlt0001','11',18467,215.98
union all select 'hlt0007','11',18467,215.98
select * from test order by belongtodrop table test
belongto :业务员编号
bemonth :月份
goodsqty :销售数量
moneytotalrmb:销售金额要转换成如下格式
业务员 月份1数量、月份1金额、月份2数量、月份2金额……月份12金额
现在问题是并不一定是1-12月份都有,也许只有1-5月份的数据,但仍要求格式如上。
数据是由出货表生成的临时表,没有主键。
*/
--以下为测试环境,谢谢各位.
if exists (select * from sysobjects where id = object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST]create table test
(belongto char(8),
bemonth char(2),
goodsqty float,
moneytotalrmb money)insert test(belongto,bemonth,goodsqty,moneytotalrmb)
select 'hlt0026','01',14312,455.23
union all select 'hlt0001','01',18767,2445.2
union all select 'hlt0015','08',14567,265.5
union all select 'hlt0009','09',18600,2445.1
union all select 'hlt0001','11',18467,215.98
union all select 'hlt0007','11',18467,215.98
select * from test order by belongtodrop table test
max(case when bemonth='01' then goodsqty end) as qty01,
max(case when bemonth='01' then moneytotalrmb end) as money01,
max(case when bemonth='02' then goodsqty end) as qty02,
max(case when bemonth='02' then moneytotalrmb end) as money02,
max(case when bemonth='03' then goodsqty end) as qty03,
max(case when bemonth='03' then moneytotalrmb end) as money03,
max(case when bemonth='04' then goodsqty end) as qty04,
max(case when bemonth='04' then moneytotalrmb end) as money04,
max(case when bemonth='05' then goodsqty end) as qty05,
max(case when bemonth='05' then moneytotalrmb end) as money05,
max(case when bemonth='06' then goodsqty end) as qty06,
max(case when bemonth='06' then moneytotalrmb end) as money06,
max(case when bemonth='07' then goodsqty end) as qty07,
max(case when bemonth='07' then moneytotalrmb end) as money07,
max(case when bemonth='08' then goodsqty end) as qty08,
max(case when bemonth='08' then moneytotalrmb end) as money08,
max(case when bemonth='09' then goodsqty end) as qty09,
max(case when bemonth='09' then moneytotalrmb end) as money09,
max(case when bemonth='10' then goodsqty end) as qty10,
max(case when bemonth='10' then moneytotalrmb end) as money10,
max(case when bemonth='11' then goodsqty end) as qty11,
max(case when bemonth='11' then moneytotalrmb end) as money11,
max(case when bemonth='12' then goodsqty end) as qty12,
max(case when bemonth='12' then moneytotalrmb end) as money12
from test
group by belongto暂时这么写吧。