select未知字段加别名
有如下代码：
declare @OrderNoVarchar(10)
declare @ShipDateVarchar(10)
declare @sql1 varchar(8000)
set @OrderNo='126608A'
set @ShipDate='2012/8/29'
set @sql1='select Color CuttingColor'
select @sql1=@sql1+',['+dd+']=sum(case Sizx when '''+dd+''' then CuttingQty else 0 end)'
from (select distinct dd=Sizx from Outward_PO where OrderNo=@OrderNo) ss inner join BSsizx on BSsizx.sizx=ss.dd order by BSsizx.seqno
set @sql1=@sql1+' into ##CuttingQty from Outward_PO where OrderNo='+''''+@OrderNo+''''+' and ShipDate='+''''+@ShipDate+''''+ 'group by Color'
exec(@sql1)
Select * from ##CuttingQty
得到如下结果：
CuttingColorXS S M L XL XXL
絳藍 0 1438 37 14 0
櫻紅 0 4 5 7 10 2
我想要得到这样的结果：
CuttingColorXS-1 S-1 M-1 L-1 XL-1 XXL-1
絳藍 0 14 38 37 14 0
櫻紅 0 4 5 7 10 2
就是说，不知道转成二维之后那些尺码到底是什么，不过需要将这些字段后面都加上一个特殊符号。
谢谢了！
[解决办法]
DECLARE @OrderNo VARCHAR(10)
DECLARE @ShipDate VARCHAR(10)
DECLARE @sql1 VARCHAR(8000)
SET @OrderNo = '126608A'
SET @ShipDate = '2012/8/29'
SET @sql1 = 'select Color CuttingColor'
SELECT @sql1 = @sql1 + ',' + dd + '+''-1''=sum(case Sizx when ''' + dd
+ ''' then CuttingQty else 0 end)'
FROM ( SELECT DISTINCT
dd = Sizx
FROM Outward_PO
WHERE OrderNo = @OrderNo
) ss
INNER JOIN BSsizx ON BSsizx.sizx = ss.dd
ORDER BY BSsizx.seqno
SET @sql1 = @sql1 + ' into ##CuttingQty from Outward_PO where OrderNo='
+ '''' + @OrderNo + '''' + ' and ShipDate=' + '''' + @ShipDate + ''''
+ 'group by Color'
EXEC (@sql1)
SELECT *
FROM ##CuttingQty
