1.确定转列的字段总数,比如我们要将商品转成列,客户购买的商品数为行的内容
我们要先要计算商品数将商品弄成(‘001’,‘002’)这种形式的
DECLARE @ZS INT,@SPDMSTR VARCHAR(4000),@i int
select @ZS=count(1) from MDI_SCCYSP
set @i=1
while @i<=@ZS
begin
set @SPDMSTR= ISNULL(@SPDMSTR+',','')+QUOTENAME((select spdm from (select *,ROW_NUMBER() over (order by id) as rn from MDI_SCCYSP cysp )a where rn=@i))
set @i=@i +1
end
列转化
DECLARE @sql VARCHAR(4000)
set @sql='
select * from (
select d.KHDM,khmc,mx.SPDM,mx.SL from MDI_XS_PFXHDMX mx
left join MDI_XS_PFXHD d on d.DJBH=mx.DJBH
left join MDI_KEHU MK on d.KHDM = MK.KHDM
where d.KHDM in (''KH102'',''KH105'',''KH104'',''KH103'')
)a PIVOT(sum(sl) FOR [SPDM] IN( '+@SPDMSTR+' )) AS T
'
exec (@sql)