34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE ##tb(sku VARCHAR(100),qty DECIMAL(10,4))
INSERT INTO ##tb(sku,qty)
SELECT '001',1.0000 UNION ALL
SELECT '001',1.0000 UNION ALL
SELECT '123',1.0000 UNION ALL
SELECT '234',1.0000 UNION ALL
SELECT 'qbc',1.0000 UNION ALL
SELECT 'qbc',2.0000
DECLARE @sql NVARCHAR(2000),@col NVARCHAR(1000)
SELECT @sql=ISNULL(@sql+',','')+QUOTENAME ( a.rn),@col=ISNULL(@col+',','')+QUOTENAME ( a.rn) +' as ' +QUOTENAME(a.sku)
FROM (
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn, * FROM ##tb AS t
) AS a
SELECT @sql,@col
SET @sql= 'SELECT '+@col +'
FROM (
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn, t.qty FROM ##tb AS t
) AS a
PIVOT (MAX(qty) FOR rn IN ('+@sql+')) b'
PRINT @sql
EXEC(@sql)
/*
001 001 123 234 qbc qbc
1.0000 1.0000 1.0000 1.0000 1.0000 2.0000
*/
select line.skucode, --商品名
line.Quantity
INTO #1
from SalesOrderLine line with(nolock) --订单明细
inner join SalesOrder so with(nolock) on line.SalesOrderHeaderId=so.Order_ID --订单
inner join Product p with(nolock) on p.Product_Id=line.ProductId --商品
left join Product_Category ca with(nolock) on ca.Category_Id=p.Category1_Id --商品分类
where so.PlatformType in ('1','5','37') and line.IsDeleted=0 and line.IsRefunded=0
and so.PlatformType=5
DECLARE @Str NVARCHAR(max)=''
SET @Str=(SELECT ','+QUOTENAME('Col'+RTRIM(ROW_NUMBER()OVER(ORDER BY RAND())))+'='''+skucode+'''' FROM #1 FOR XML PATH(''))
SET @Str=STUFF(@Str,1,1,'SELECT ')+STUFF((SELECT ','+QUOTENAME('Col'+RTRIM(ROW_NUMBER()OVER(ORDER BY RAND())))+'='''+RTRIM(ISNULL(Quantity,0))+'''' FROM #1 FOR XML PATH('')),1,1,' UNION ALL SELECT ')
EXEC(@Str)