SQLSERVER 动态行转列

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)