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 #jjDeclare @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 150OrderNo 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?该怎么写才对?
(
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 #jjDeclare @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 150OrderNo 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?该怎么写才对?
from #jj
pivot(max(qty) for sizx in ([M],[XL],[XS],[XXS]) as b
declare @sizx varchar(2000)=''
select @sizx=@sizx+','+quotename(sizx) from #A
select @sizx
后面自己写了...
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)
补了个括号。
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'
exec(@str)
1 105800A 藍色 XL 2
2 105800A 黃色 XL 2
3 105800A 紅色 XL 1
4 105800A 紅色 XL 2
5 105800A 紅色 XXS 1
6 105800A 黑色 XXS 11
7 105800A 藍色 XXS 12
8 105800A 黃色 XXS 13
9 105800A 紅色 XS 10
10 105800A 黑色 XS 15
11 105800A 藍色 XS 16
12 105800A 黃色 XS 120
13 105800A 紅色 M 50
14 105800A 藍色 M 150
然后以下是照搬你的语句:declare @sizx varchar(2000)='',@str varchar(max)=''
select @sizx=@sizx+','+quotename(sizx) from bb group by sizx
select @sizx=STUFF(@sizx,1,1,'')
set @str='select * from bb pivot(max(qty) for sizx in ('+@sizx+')) as b'
exec(@str)
结果有这么一堆提示:Server: Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@sizx'.
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@sizx'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@sizx'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@str'.
declare @sizx varchar(2000)
declare ,@str varchar(max)
set @sizx=''
set @str=''
select @sizx=@sizx+','+quotename(sizx) from bb group by sizx
select @sizx=STUFF(@sizx,1,1,'')
set @str='select * from bb pivot(max(qty) for sizx in ('+@sizx+')) as b'
exec(@str)
declare @sizx varchar(2000)
declare ,@str varchar(max)
set @sizx=''
set @str=''
select @sizx=@sizx+','+quotename(sizx) from bb group by sizx
select @sizx=STUFF(@sizx,1,1,'')
set @str='select * from bb pivot(max(qty) for sizx in ('+@sizx+')) as b'
exec(@str)