Create Table #JJ
(
OrderNo Varchar(12),
ColorDesc Varchar(50),
Sizx Varchar(8),
Qty Int
)
Insert into #jj (OrderNo,ColorDesc,Sizx,Qty)
Select
POPackingDetail.OrderNo,
POColor.ColorDesc,
POPackingDetail.Sizx,POPackingDetail.Qty
from
POPackingDetail inner join POColor on POColor.ColorID=POPackingDetail.ColorID and POColor.OrderNo=POPackingDetail.OrderNo
where
POPackingDetail.OrderNo='105800A'
Select * from #jj
Declare @sql Varchar(8000)
Set @sql='Select OrderNo,ColorDesc'
Select @sql=@sql+',['+dd+']=sum (case Sizx when ''+dd+'' then Qty else 0 end)'
from (Select distinct dd=Sizx from #jj) ss
Set @sql=@sql+'from #jj Group by OrderNo,ColorDesc'
Exec(@sql)
Drop Table #jj
得出以下结果：
OrderNo ColorDesc Sizx Qty
105800A 蓝色 XL 2
105800A 黄色 XL 2
105800A 红色 XL 1
105800A 红色 XL 2
105800A 红色 XXS 1
105800A 黑色 XXS 11
105800A 蓝色 XXS 12
105800A 黄色 XXS 13
105800A 红色 XS 10
105800A 黑色 XS 15
105800A 蓝色 XS 16
105800A 黄色 XS 120
105800A 红色 M 50
105800A 蓝色 M 150
OrderNo ColorDesc M XL XS XXS
105800A 红色 0 0 0 0
105800A 黄色 0 0 0 0
105800A 黑色 0 0 0 0
105800A 蓝色 0 0 0 0
我想问的是，一维转二维那个结果，为什么都是0？该怎么写才对？
------解决方案--------------------
select *
from #jj
pivot(max(qty) for sizx in ([M],[XL],[XS],[XXS]) as b
------解决方案--------------------
你这条语句，怎么看都像是锁死了尺码，而且，怎么用MAX？
------解决方案--------------------
锁死了尺码，是不是指尺码并非只有四个? 如果尺码不固定可以写成动态的
declare @sizx varchar(2000)=''
select @sizx=@sizx+','+quotename(sizx) from #A
select @sizx
后面自己写了...
------解决方案--------------------
能够不能给个完整的代码？后面真的不会写......
------解决方案--------------------
declare @sizx varchar(2000)='',@str varchar(max)=''
select @sizx=@sizx+','+quotename(sizx) from #A
select @sizx=STUFF(@sizx,1,1,'')
set @str='select * from #jj pivot(max(qty) for sizx in ('+@sizx+') as b'
exec(@str)
------解决方案--------------------
set @str='select * from #jj pivot(max(qty) for sizx in ('+@sizx+')) as b'
补了个括号。
------解决方案--------------------
这个是完整的：
declare @sizx varchar(2000)='',@str varchar(max)=''
select @sizx=@sizx+','+quotename(sizx) from #A group by sizx
select @sizx=STUFF(@sizx,1,1,'')
set @str='select * from #jj pivot(max(qty) for sizx in ('+@sizx+')) as b'