with cte as (select *,ROW_NUMBER()over(partition by CKDM order by rq) as n from test), t1 as (select ckdm,ckmc,[1]as 昨天销售总金额,[2] as 今天销售总金额 from (select ckdm,ckmc,je,n from cte)as t pivot (max(je) for n in([1],[2],[3])) as p), t2 as (select ckdm,[1]as 昨天销售数量,[2] as 今天销售数量 from (select ckdm,sl,n from cte)as t pivot (max(sl) for n in([1],[2],[3])) as p), t3 as (select ckdm,[1]as 昨天销售原价,[2] as 今天销售原价 from (select ckdm,ckj,n from cte)as t pivot (max(ckj) for n in([1],[2],[3])) as p) select t1.*,t2.今天销售数量,t2.昨天销售数量,t3.今天销售原价, t3.昨天销售原价 from t1 join t2 on t1.ckdm=t2.ckdm join t3 on t1.ckdm=t3.ckdm
区域1 原价总额 1
区域1 原价总额 2
区域1 销售总额 3
区域1 销售总额 1
区域1 销售总额 2
区域1 销售总额 3
……
然后进行行列转换即可
ckdm ckmc sl je ckj rq
----------- ---------- ----------- ----------- ----------- ----------
810 南京长发 16 10878 12754 2014-09-25
803 南京山百 15 10365 12545 2014-09-25
810 南京长发 32 9586 27078 2014-09-26
803 南京山白 24 15284 9029 2014-09-26(4 行受影响)
with cte as
(select *,ROW_NUMBER()over(partition by CKDM order by rq)
as n from test),
t1 as
(select ckdm,ckmc,[1]as 昨天销售总金额,[2] as 今天销售总金额 from
(select ckdm,ckmc,je,n from cte)as t
pivot (max(je) for n in([1],[2],[3])) as p),
t2 as
(select ckdm,[1]as 昨天销售数量,[2] as 今天销售数量 from
(select ckdm,sl,n from cte)as t
pivot (max(sl) for n in([1],[2],[3])) as p),
t3 as
(select ckdm,[1]as 昨天销售原价,[2] as 今天销售原价 from
(select ckdm,ckj,n from cte)as t
pivot (max(ckj) for n in([1],[2],[3])) as p)
select t1.*,t2.今天销售数量,t2.昨天销售数量,t3.今天销售原价,
t3.昨天销售原价 from t1 join t2 on t1.ckdm=t2.ckdm
join t3 on t1.ckdm=t3.ckdm
ckdm ckmc 昨天销售总金额 今天销售总金额 今天销售数量 昨天销售数量 今天销售原价 昨天销售原价
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
810 南京长发 10878 9586 32 16 27078 12754
803 南京山百 10365 15284 24 15 9029 12545(2 行受影响)