尺码打横

SELECT CangKuMC,BianMa,MingCheng,YanSeTM,YanSeMC,ShuLiang,ChiMaMC,LingShouJ,zhuGongYS

into #xiaoshoud1

FROM [dbo].[v_QiMo_KuCunSP_XiMu] where 1=1

DECLARE @sql varchar(4000)

SET @sql = 'select zhuGongYS,CangKuMC,BianMa 款号,MingCheng 名称,YanSeTM 颜色代号, YanSeMC 颜色 '

select @sql = @sql + ',sum(case ChiMaMC when '''+ChiMaMC+''' then ShuLiang end) as '''+ChiMaMC+'''' from (select mc ChiMaMC from cm where mc in (SELECT distinct ChiMaMC from #xiaoshoud1 )) as a

select @sql=@sql+',sum(ShuLiang)合计,LingShouJ into #te from #xiaoshoud1

group by zhuGongYS,CangKuMC,BianMa,MingCheng,YanSeTM,YanSeMC,LingShouJ order by BianMa

select row_number()over(order by 款号) xh, 款号 into #tempB from #te group by 款号

select 款号,sum(合计) 款总数 into #tempA from #te group by 款号

SELECT b.xh 序号,A.款号,A.名称,zhuGongYS 波段,A.颜色代号,A.颜色,LingShouJ 零售价,均码,S,M,L,XL,XXL,XXXL,36,40,a.合计,c.款总数,LingShouJ*a.合计 as 金额,

CangKuMC 所在店铺 FROM #te A

LEFT JOIN #tempB B ON A.款号=B.款号

LEFT JOIN #tempA C ON C.款号=A.款号

DROP TABLE #te DROP TABLE #tempB '

EXEC(@sql)

drop table #xiaoshoud1      --