Hi Nimish,
If your table “[Stage].[Table]” has many columns, you’d better modify your code as following shows to first filter the related columns as the source query for
PIVOT. For detailed information, you could have a look at
this blog.
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
--Get unique values of pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Name]) FROM (SELECT [Name] FROM [Stage].[Table] group by [Name] ) AS PivotQuery
--SELECT @PivotColumns
--Dynamic Query
SET @SQLQuery =
N'SELECT [Object_Key]
,[ObjectType]
,[SourceSystemCode]
, ' + @PivotColumns + '
FROM (select [Object_Key],[ObjectType],[SourceSystemCode],[name] from [Stage].[Table] where [name] in ('+@PivotColumns +')) s
PIVOT( MAX(Value)
FOR [Name] IN (' + @PivotColumns + ')) AS P'
--SELECT @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery
Then, you could check the execution plan of your query. In your case, you could try to create an index on “[Object_Key],[ObjectType],[SourceSystemCode],[name]” to make your query be better. For further things, please refer to following blog and
this similar thread.
https://blogs.msdn.microsoft.com/craigfr/2007/07/09/pivot-query-plans/
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found
on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions
you to make sure that you completely understand the risk before retrieving any software from the Internet.
Best Regards,
Albert Zhang
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.