產品查詢 動態樞紐

/*指定的產品查詢  動態樞紐*/

WITH C1
AS
(
	SELECT A.客戶編號 ,C.產品
	FROM 訂貨主檔 AS A INNER JOIN 訂貨明細 AS B ON A.訂單號碼=B.訂單號碼
		JOIN 產品資料 AS C ON B.產品編號=C.產品編號
	WHERE B.產品編號 = 11
	GROUP BY A.客戶編號 ,C.產品
),
C2
AS(

SELECT D.客戶編號 ,C.產品,COUNT(C.產品) AS 次數,
        ROW_NUMBER()OVER(PARTITION BY D.客戶編號 ORDER BY COUNT(C.產品) DESC) 排序
FROM 訂貨主檔 AS A 
	JOIN 訂貨明細 AS B ON A.訂單號碼=B.訂單號碼
	JOIN 產品資料 AS C ON B.產品編號=C.產品編號
	JOIN C1 AS D ON A.客戶編號=D.客戶編號
WHERE B.產品編號<>11
GROUP BY D.客戶編號 ,C.產品

),
C3
AS(
SELECT C2.客戶編號,C2.產品,C2.排序
FROM C2
WHERE 排序 <4
)

SELECT*
INTO #R1
FROM C3
GO
			DECLARE @col NVARCHAR (11)---宣告變數來排序
			SELECT @col=STRING_AGG(QUOTENAME([排序]),',') FROM #R1
		
			PRINT @col

			EXECUTE('
			SELECT 客戶編號,'+@col+'
			FROM #R1
			PIVOT
			(
			  MAX(產品) 
			  FOR 排序 IN('+ @col +')
			) AS PVT');