Hi All,
I got into a situation where I have to apply case statement on Dynamically generated columns. Below is my requirement and how I how I achieved that.
I have created below result set with pivoting. Actually Server1,server2,server3 … are Rows and I am making them as columns. I am generating the servers information as columns dynamically
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | NULL | NOT EXIST |
| Assembly2 | NOT EXIST | NULL | NOT EXIST |
| Assembly3 | NOT EXIST | NULL | NOT EXIST |
I want the result like this.
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | EXIST | NOT EXIST |
| Assembly2 | NOT EXIST | EXIST | NOT EXIST |
| Assembly3 | NOT EXIST | EXIST | NOT EXIST |
Below is the solution for this.
–Below is the Dynamic SQL Pivot Script.
DECLARE @servers nvarchar(max)
SELECT @servers =
STUFF(
(
select distinct ‘,[‘ + S.ServerName + ‘]’
from tempassemblies S
for xml path(”)
),
1,1,”)
DECLARE @SQL nvarchar(max)
SELECT @SQL = N’
select
* into Temp_Assemblies
from (
SELECT p.AssemblyName, p.ServerName,p.CurrentStatus
FROM dbo.tempassemblies p
) Data
PIVOT (
max(CurrentStatus )
FOR [ServerName]
IN (
‘ + @servers + ‘
)
) PivotTable
‘
exec sp_executesql @SQL
–update NULL values to ‘Custom Values’
DECLARE @dynamicsql nvarchar(MAX)
SELECT @dynamicsql= COALESCE(@dynamicsql + ‘ ‘, ”) +
‘UPDATE Temp_Assemblies SET ‘ + quotename(name)+ ‘ = ”EXIST” WHERE ‘ + quotename(name) + ‘ IS NULL ‘
FROM sys.columns WHERE object_id = object_id(‘Temp_Assemblies’)
EXECUTE (@dynamicsql)
Regards,
Chaitanya
SQL BLOGGING 