一维转二维
Create Table #JJ
(
OrderNoVarchar(12),
ColorDescVarchar(50),
SizxVarchar(8),
QtyInt
)
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 @sqlVarchar(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
得出以下结果：
OrderNoColorDescSizxQty
105800A蓝色XL2
105800A黄色XL2
105800A红色XL1
105800A红色XL2
105800A红色XXS1
105800A黑色XXS11
105800A蓝色XXS12
105800A黄色XXS13
105800A红色XS10
105800A黑色XS15
105800A蓝色XS16
105800A黄色XS120
105800A红色M50
105800A蓝色M150
OrderNoColorDescMXLXSXXS
105800A红色0000
105800A黄色0000
105800A黑色0000
105800A蓝色0000
我想问的是，一维转二维那个结果，为什么都是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'
exec(@str)
[解决办法]
我创建了这么一个实表：bb
IDOrderNoColorDescSizxQty
1105800A色XL2
2105800A色XL2
3105800A色XL1
4105800A色XL2
5105800A色XXS1
6105800A黑色XXS11
7105800A色XXS12
8105800A色XXS13
9105800A色XS10
10105800A黑色XS15
11105800A色XS16
12105800A色XS120
13105800A色M50
14105800A色M150
然后以下是照搬你的语句：
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'.
[解决办法]
你用的05吧？ 用下面的
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)
[解决办法]
我用的是SQL2000，你的语句，还是有一堆提示：
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)
[解决办法]
PIVOT是SQL Server 2005 的语法，2000 用case when 吧
[解决办法]
不好意思，不知道如何改......劳驾！