筆記記憶

• 櫃點數量不一定
• 依實際銷售之貨號列表

DECLARE @SessionId varchar(50)='201802TPE'
DECLARE @cols nvarchar(max)=N''	--儲存動態欄位之用
-- PIVOT
SELECT @cols=@cols+ IIF(@cols=N'','['+a.CustomerStorerId+']',N',['+a.CustomerStorerId+']')
FROM (SELECT b.CustomerStorerId 
	FROM TinyPos.dbo.OrderMaster a 
		LEFT JOIN TinyPos.dbo.StorerInfo b ON a.StorerId=b.StorerId
	WHERE SessionId=@SessionId 
	GROUP BY b.CustomerStorerId
	) a
PRINT @cols

--組動態SQL並使用sp_executesql來執行
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT Sku, ' + @cols + '
FROM
(
	SELECT b.Sku
		, c.CustomerStorerId
		, b.Qty
	FROM TinyPos.dbo.OrderMaster a 
		LEFT JOIN TinyPos.dbo.OrderDetail b ON a.OrderId=b.OrderId 
		LEFT JOIN TinyPos.dbo.StorerInfo c ON a.StorerId=c.StorerId
) AS j
PIVOT
(
	SUM(Qty)
	FOR CustomerStorerId IN (' + @cols + ')
) AS t'

PRINT @sql
EXEC sp_executesql @sql

 

IIF 語法是 SQL 2012後才有，之前的版本可以用CASE WHEN 來替代

DECLARE @WAVEKEY char(10)='0000078982'
DECLARE @StorerKey char(15)='D003A'
DECLARE @cols nvarchar(max)=N''	--儲存動態欄位之用


-- PIVOT
SELECT @cols=@cols + CASE WHEN @cols='' THEN '['+a.C_Company+']' ELSE ',['+a.C_Company+']' END
FROM (SELECT C_Company
	FROM Trident.dbo.ORDERS
	WHERE StorerKey=@StorerKey 
		AND Wavekey=@WAVEKEY 
	GROUP BY C_Company) a 
PRINT @cols

DECLARE @sql NVARCHAR(MAX)
	SET @sql = N'
	SELECT Sku, ' + @cols + '
	FROM
	(
		SELECT a.Sku
			, b.C_Company
			, a.Qty
		FROM Trident.dbo.PickDetail a 
			LEFT JOIN Trident.dbo.Orders b ON a.OrderKey=b.OrderKey
		WHERE a.WaveKey= ''' + @WAVEKEY + '''
	) AS j
	PIVOT
	(
		SUM(Qty)
		FOR C_Company IN (' + @cols + ')
	) AS t'

	PRINT @sql