I need to create a dynamic PIVOT table that I can join to. I'm currently using a VIEW, but this is a statically created VIEW that has to be manually modified as the potential values change.I thought I was going to be smart and create a multi-statement table valued function, but I now see that you have to define the table that you will return when you define the function, which creates the same problem.So I could use a stored proc to make it dynamic, but I can't join to a stored proc and I need to be able to join to this table.So, is there any solution here? Is there any way to make a UDF or something else that would allow for the encapsulation of a PIVOT result set that can be joined to?Here is my code:DECLARE @cols NVARCHAR(2000);DECLARE @query NVARCHAR(4000);SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + [column_name] FROM dbo.ref_ExclusionReasons ORDER BY '],[' + [column_name] FOR XML PATH('') ), 1, 2, '') + ']';SET @query = N'SELECT vid_transmission, vcid_term,'+ @cols + 'FROM (SELECT [vid_transmission], [vcid_term], [column_name] FROM dbo.Exclusions) p PIVOT ( COUNT([column_name]) FOR [column_name] IN ( '+@cols +' ) ) AS pvt;';EXECUTE(@query);--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware
Dynamic PIVOT Table Valued Function...
Posted - 2012-10-26 : 18:13:55
