在Dynamic Pivot中包含WHERE子句

Question

我创建了一个工作动态数据透视表。

DECLARE 
@AttributeNames as NVARCHAR(MAX), 
@SQL as NVARCHAR(MAX); 

DECLARE 
@AssetGroup as NVARCHAR(MAX) = 'Water', 
@AssetType as NVARCHAR(MAX)= 'Water Meters', 
@RecStatus as NVARCHAR(MAX) = 'I'; 



SET @AttributeNames = STUFF(
(SELECT N',' + QUOTENAME(AttributeName) AS [text()] 
    FROM (SELECT DISTINCT G1_ATTRIBUTE_NAME as AttributeName 
     FROM dbo.GASSET_MASTER INNER JOIN 
         dbo.GASSET_ATTRIBUTE ON dbo.GASSET_MASTER.SERV_PROV_CODE = dbo.GASSET_ATTRIBUTE.SERV_PROV_CODE AND 
         dbo.GASSET_MASTER.G1_ASSET_SEQ_NBR = dbo.GASSET_ATTRIBUTE.G1_ASSET_SEQ_NBR 
    --WHERE  (dbo.GASSET_MASTER.G1_ASSET_GROUP = ''' + @AssetGroup + ''') AND (dbo.GASSET_MASTER.G1_ASSET_TYPE = ''' + @AssetType + ''') 
    ) AS AttributeName 

ORDER BY AttributeName 

FOR XML PATH('')), 
1,1, N''); 



SET @SQL = N'SELECT * 
FROM (SELECT  dbo.GASSET_MASTER.G1_ASSET_ID, dbo.GASSET_ATTRIBUTE.G1_ASSET_SEQ_NBR, dbo.GASSET_ATTRIBUTE.G1_ATTRIBUTE_NAME, 
         dbo.GASSET_ATTRIBUTE.G1_ATTRIBUTE_VALUE, dbo.GASSET_MASTER.G1_ASSET_GROUP, dbo.GASSET_MASTER.G1_ASSET_TYPE, 
         dbo.GASSET_MASTER.G1_ASSET_STATUS, dbo.GASSET_MASTER.REC_STATUS 
     FROM   dbo.GASSET_MASTER LEFT OUTER JOIN 
         dbo.GASSET_ATTRIBUTE ON dbo.GASSET_MASTER.SERV_PROV_CODE = dbo.GASSET_ATTRIBUTE.SERV_PROV_CODE AND 
         dbo.GASSET_MASTER.G1_ASSET_SEQ_NBR = dbo.GASSET_ATTRIBUTE.G1_ASSET_SEQ_NBR 
     WHERE  (dbo.GASSET_MASTER.G1_ASSET_GROUP = ''' + @AssetGroup + ''') AND (dbo.GASSET_MASTER.G1_ASSET_TYPE = ''' + @AssetType + ''') AND (dbo.GASSET_MASTER.REC_STATUS <> ''' + @RecStatus + ''')) as SState 

PIVOT (
MAX(G1_ATTRIBUTE_VALUE) 
FOR G1_ATTRIBUTE_NAME IN(' + @AttributeNames + N')) AS P;'; 


Print @SQL 

此代码导致我认为是一个正确格式化的枢轴。但是，当我在构建列时尝试使用注释掉的WHERE子句时，不会返回任何记录，也不会收到错误。

--WHERE  (dbo.GASSET_MASTER.G1_ASSET_GROUP = ''' + @AssetGroup + ''') AND (dbo.GASSET_MASTER.G1_ASSET_TYPE = ''' + @AssetType + ''') 

我试图以许多不同的方式更改WHERE子句，但没有成功。任何想法如何我可以做到这一点不同？

Answer 1

更改注释行这一个：

WHERE  (dbo.GASSET_MASTER.G1_ASSET_GROUP = @AssetGroup) AND (dbo.GASSET_MASTER.G1_ASSET_TYPE = @AssetType) 

说明：第一次查询不是动态查询，你不需要嵌入'