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
Throw Statement in Sql server 2012
In 2012, raise error is replaced by throw statement
If we executed the below script, we will have the raise error statement that will catch error
Raise error have some limitation it will not flow the control to the catch block in some severities. Please execute the below query to see the limitation
For severity 10, it will not flow the control.
So we don’t have error raised.
Throw will eliminate these limitations.
Syntax of throw:
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]
Parameters:
Error_number:
Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.
The error_number parameter does not have to be defined in sys.messages
Message:
Is an string or variable that describes the exception. message is nvarchar(2048).
State:
Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.
There is no severity parameter. The exception severity is always set to 16.
We have to specify throw statement inside begin try with parameters
We should not specify throw without parameters in try block
Throw statement should end with semi colon
SQL BLOGGING 