临时表转置

USE [MES.WGBEYOND]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

ALTER PROC [DBO].[SP_GETMONOTICE]
@CUSTOMERNAME NVARCHAR(50)
AS
BEGIN
DECLARE @SQL VARCHAR(500)
DECLARE @STUFF NVARCHAR(MAX)

--新建临时表#TEMP
SELECT MO.MONO,MO.CUSTOMERNAME,MO.PRODUCTCODE,OP.OPERATIONNAME,
SUM(LT.QTY)QTY INTO #TEMP FROM LOT AS LT
INNER JOIN OPERATION AS OP ON LT.OPERATIONID=OP.OPERATIONID
INNER JOIN MO ON MO.MONO=LT.MONO
WHERE MO.CustomerName=@CUSTOMERNAME
GROUP BY MO.MONO,MO.CUSTOMERNAME,MO.PRODUCTCODE,OP.OPERATIONNAME

-- 置换 固定
SET @SQL='SELECT * FROM
(
SELECT A.MONO,A.CUSTOMERNAME,A.PRODUCTCODE,A.OPERATIONNAME,A.QTY FROM #TEMP AS A
)P
PIVOT(
SUM(QTY) FOR OPERATIONNAME IN
([AOI检验],[磨边],[内箱工序],[入库工序],[外箱工序],[重来来料检]))
AS PVT'
EXEC(@SQL);

 

 -- 置换 动态

SELECT @STUFF = STUFF(
(SELECT DISTINCT','+O.OPERATIONNAME FROM MO M
INNER JOIN LOT L ON L.MONO = M.MONO
INNER JOIN OPERATION O ON O.OPERATIONID = L.OPERATIONID
FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT * FROM (
SELECT A.MONO,A.CUSTOMERNAME,A.PRODUCTCODE,A.OPERATIONNAME,A.QTY FROM #TEMP AS A
)AS T PIVOT (SUM(QTY) FOR OPERATIONNAME IN ('+@STUFF+')) AS A'
EXEC (@SQL)

END