Case Statement on Dynamically Generated Pivot Columns


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: