DECLARE @processID AS VARCHAR(MAX),
@query AS VARCHAR(MAX)
SET @processID=STUFF((SELECT ','+QUOTENAME( ParameterCaption+'//'+CAST(ProcessParameterID AS VARCHAR)+'//'+ProcessName) FROM dbo._TBL_RnDProcessParameter R INNER JOIN dbo._TBL_RnDProcess P ON P.RnDProcessID=R.ProcessID
WHERE R.IsActive=1 FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
SET @query=' SELECT RnDNo,BuyerName,OrderNo,SampleType,Color,Note,RequiredWidth,RequiredGSM,'+@processID+' FROM
(SELECT * FROM
(SELECT ProcessDataHeadID,ParameterCaption+''//''+CAST(A.ProcessParameterID AS VARCHAR)+''//''+ProcessName AS FabricHead, COALESCE(Value,'''')Value FROM
(SELECT ParameterCaption,ProcessParameterID,ProcessID FROM dbo._TBL_RnDProcessParameter
WHERE IsActive=1)A
LEFT OUTER JOIN
(SELECT ProcessParameterID,Value,ProcessDataHeadID FROM dbo._TBL_RnDProcessData)B
ON B.ProcessParameterID=A.ProcessParameterID
INNER JOIN
dbo._TBL_RnDProcess P ON P.RnDProcessID=A.ProcessID
)A
PIVOT(MAX(Value) FOR FabricHead IN ('+@processID+'))P
WHERE ProcessDataHeadID IS NOT NULL
)A
LEFT OUTER JOIN
dbo._TBL_RnDProcessDataHead H
ON H.ProcessDataHeadID=A.ProcessDataHeadID
';
EXEC(@query);
@query AS VARCHAR(MAX)
SET @processID=STUFF((SELECT ','+QUOTENAME( ParameterCaption+'//'+CAST(ProcessParameterID AS VARCHAR)+'//'+ProcessName) FROM dbo._TBL_RnDProcessParameter R INNER JOIN dbo._TBL_RnDProcess P ON P.RnDProcessID=R.ProcessID
WHERE R.IsActive=1 FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
SET @query=' SELECT RnDNo,BuyerName,OrderNo,SampleType,Color,Note,RequiredWidth,RequiredGSM,'+@processID+' FROM
(SELECT * FROM
(SELECT ProcessDataHeadID,ParameterCaption+''//''+CAST(A.ProcessParameterID AS VARCHAR)+''//''+ProcessName AS FabricHead, COALESCE(Value,'''')Value FROM
(SELECT ParameterCaption,ProcessParameterID,ProcessID FROM dbo._TBL_RnDProcessParameter
WHERE IsActive=1)A
LEFT OUTER JOIN
(SELECT ProcessParameterID,Value,ProcessDataHeadID FROM dbo._TBL_RnDProcessData)B
ON B.ProcessParameterID=A.ProcessParameterID
INNER JOIN
dbo._TBL_RnDProcess P ON P.RnDProcessID=A.ProcessID
)A
PIVOT(MAX(Value) FOR FabricHead IN ('+@processID+'))P
WHERE ProcessDataHeadID IS NOT NULL
)A
LEFT OUTER JOIN
dbo._TBL_RnDProcessDataHead H
ON H.ProcessDataHeadID=A.ProcessDataHeadID
';
EXEC(@query);
No comments:
Post a Comment