SQL Server - Dynamic Pivoting

In this post, let us see an example for dynamic pivoting.



CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
INSERT INTO TMP SELECT 'SQL Server',1
INSERT INTO TMP SELECT 'Oracle',2

--Example for dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'') 
   
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;


SQL Server - SET DATEFIRST and @@DATEFIRST

SELECT * FROM sys.syslanguages

Above query will return resultset(date & language details) as shown in below image:


--@@LANGUAGE will give us the language detail for the current session
--@@DATEFIRST will give us the "first day of a week" value for the current session
--for language 'us_english', default value (first day of a week) = 7

 
SELECT @@LANGUAGE Currentsession_Language
SELECT @@DATEFIRST Defaultvalue_for_usenglish

--to override the default value (first day of a week) = 6 for this current session alone,use SET DATEFIRST

 
SET DATEFIRST 6
SELECT @@DATEFIRST Overriddenvalue_first_day_of_week 



SQL Server - Split CSV into separate row values

In this post, let us see how to split delimited string into separate rows using User defined function.

--Example for splitting CSV into separate row values


SQL Server - Combine separate row values as CSV

In this post, let us see how to combine values from separate rows into delimited string.
Example 2 in this post, shows how to combine values from separate grouped records into delimited string.

Example 1:





--Examples for Combining separate row values as CSV 

DECLARE @csv TABLE (ID INT,
Name VARCHAR(30))
INSERT INTO @csv SELECT 1,'SQL Server'
INSERT INTO @csv SELECT 2,'Oracle'
INSERT INTO @csv SELECT 3,'DB2'
SELECT * FROM @csv

 
--Using COALESCE 
DECLARE @V VARCHAR(100)
SELECT @V = COALESCE(@V+', ','' )+Name FROM @csv 
SELECT @V CSV

 
--Using STUFF & XML PATH
SELECT STUFF( 
 (SELECT  ', ' + Name FROM 
   (SELECT Name FROM @csv ) AS T FOR XML PATH('')),1,2,'') AS CSV

 
--Using SUBSTRING & XML PATH
SELECT SUBSTRING(
 ( SELECT (', ' + Name)
  FROM @csv
  FOR XML PATH('')
 ) , 3,2000) 
AS CSV





SQL Server - Unique index to allow multiple NULL

UNIQUE Constraint/Index :

Both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness.

Unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL,however only one null value is allowed per column. 

--Example for unique index to allow multiple NULL

CREATE TABLE Unique_Duplicate (Col1 INT,Col2 VARCHAR(100))
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 IS NOT NULL
GO
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
GO
SELECT * FROM Unique_Duplicate
GO
---------------------------------------------------------
--Example Unique index to allow one particular duplicate value

SQL Server 2012 - IIF ( ) and CHOOSE ( )

IIF - Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Syntax:
IIF ( boolean_expression, true_value, false_value )
 
Examples :


SELECT IIF(1>2,'TRUE','FALSE')
GO
 -- output: FALSE 
 
 
 
SELECT IIF(2>1,'TRUE','FALSE')
GO 
 -- output: TRUE
 
 
SELECT IIF(1>2,NULL,NULL)
GO 
 -- Msg 8133, Level 16, State 1, Line 1
-- At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. 
 

DECLARE @Exp CHAR(5)
SELECT IIF(1>2,NULL,@Exp)
GO
 -- output: NULL

SQL Server 2012 - THROW - Enhancement in error handling using TRY CATCH


THROW  statement can be used to raise an exception,like RAISERROR

Syntax : 

THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

error_number
 error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.
message
message is nvarchar(2048).
state
 between 0 and 255 that indicates the state to associate with the message. state is tinyint.

EXAMPLES:

-- Exceptions raised by THROW statement will always have a severity of 16

THROW 50000, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
THROW 2147483647, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
 
--The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
USE AdventureWorks2012

CREATE PROCEDURE THROW_Example
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
DELETE FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1
END TRY

BEGIN CATCH
SELECT 'The statement before the THROW statement must be followed by the semicolon (;) statement terminator';
THROW
END CATCH

END
GO
EXECUTE THROW_Example

GO



 For more information  & differences between the RAISERROR and THROW statements - http://msdn.microsoft.com/en-us/library/ee677615.aspx
Structured Error Handling Mechanism in SQL Server 2012 -  http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx 

SQL Server 2012 - TRY_CONVERT( ) -Data type conversion function


TRY_CONVERT function tries to convert the values passed to it ,into a specified data type.


 If the conversion is successful then it will return the value of the specified data type,

 else it will return a NULL value.


Syntax :


TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
 
data_type [ ( length ) ] - The data type into which to cast expression.
                          
expression - The value to be cast         

style  -    Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.



Examples for TRY_CONVERT( ):

 USE AdventureWorks2012

--using TRY_CONVERT() , to convert integer to character 
 
SELECT TRY_CONVERT(NVARCHAR(1),1)
GO


--if the conversion fails , CONVERT ( ) will return error, 
TRY_CONVERT( ) will return NULL

SELECT CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M' to data type int.

SELECT TRY_CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1


--sufficient data type length is not specified ,CONVERT ( ) will return error, TRY_CONVERT( ) will return NULL

SELECT CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7

Msg 6354, Level 16, State 10, Line 1
Target string size is too small to represent the XML instance

SELECT TRY_CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7


--third argument in TRY_CONVERT( ) i.e ., style accepts the same values as the style parameter of the CONVERT ( ) function.

SELECT TRY_CONVERT(NVARCHAR(30),getdate(),107)
SELECT TRY_CONVERT(NVARCHAR(30),getdate(),104) 



--TRY_CONVERT returns an error when the cast is explicitly not permitted.


SELECT TRY_CONVERT(xml, 10) AS Result;
GO

     Msg 529, Level 16, State 2, Line 1
     Explicit conversion from data type int to xml is not allowed.
 
 

SQL Server 2012 - CONCAT() - String function

CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values.

The CONCAT function requires a minimum of two input values, else the concatenation fails.

SYNTAX: - 
                   CONCAT (string_value1, string_value2 [,string_valueN])


In previous versions of SQL Server, we had plus symbol " +  "  to concatenate values.
Even in SQL Server 2012, we can use plus symbol " +  "  to concatenate values.

CONCAT( )  function overcomes data type conversion problem while concatenating values of different data types, in a simple way by implicitly coverting all arguments to string types before concatenating the inputs.

Null values are implicitly converted to an empty string. 

If all the arguments are null, an empty string of type varchar(1) is returned. 

Examples for CONCAT( ) function:


USE AdventureWorks2012
--Concatenating using plus symbol "+" 
SELECT JobTitle + '|' + HireDate FROM  [HumanResources].[Employee]
GO



Msg 402, Level 16, State 1, Line 1
The data types nvarchar and date are incompatible in the add operator.

SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)

Sqlserver 2012 introduced OFFSET and FETCH clause to limit the number of rows returned by a query,after sorting the rows using ORDER BY clause.

 OFFSET clause sets how many rows needs to be skipped  
 FETCH NEXT n ROWS ONLY, displays the next n records

Note :  OFFSET and FETCH clause can be used only with ORDER BY clause

Examples for understanding  OFFSET  & FETCH clause 

USE AdventureWorks2012

--Example 1: - using only
OFFSET  with  ORDER BY clause
--In below example,after sorting rows in ascending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROW
--Example 2 - In below example,after sorting rows in descending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID DESC
     OFFSET 1 ROW
 
--Example 3 - In below example,after sorting rows ,first row is skipped & next 10 rows are fetched.   

SQL Server 2012 - WITH RESULT SETS

What is WITH RESULT SETS  in SQL Server 2012 ?

                            WITH RESULT SETS  provides flexibility in getting output of stored procedure in two ways :

                              1.) Column names returned as the result of stored procedure execution can be changed
                              2.) Data type of columns returned as the result of stored procedure execution can be changed


                             WITH RESULT SETS can be used only at the time of executing stored procedure.

Examples for understanding  WITH RESULT SETS :

USE AdventureWorks2012

--Example 1 

CREATE PROCEDURE With_Resultset
--Procedure to get top 5 married employee details
AS
BEGIN

        SELECT TOP 5 
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate 
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M'

END

--  actual column names returned by stored procedure are changed using WITH RESULT SETS

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      ) 
  ) 
GO







--Example 2 - Only compatible data type conversion is possible

-- In below example - column NationalID data type NVARCHAR(20) is converted to INT

SQL Server - Remove XML tags from XML document and combine values of XML nodes into single paragraph

Example -  Remove XML tags from XML document and convert values of XML nodes into single paragraph  


USE AdventureWorks2012

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS  "RES")
UPDATE [AdventureWorks2012].[HumanResources].[JobCandidate]
SET Resume.modify('
  insert(<?xml-stylesheet href="Resume.xsl" type="text/xsl"?>)
  before(/RES:Resume)[1]')
WHERE JobcandidateID  IS NOT NULL;

Try the below query,copy the XML document and paste it into notepad and save it as .xhtml as shown in below image:

SELECT Resume FROM [AdventureWorks2012].[HumanResources].[JobCandidate]



Now open the saved file ,you will notice as shown in below image :


SQL Server - Database Read - Only

After attaching sample database - AdventureWorks2012,I noticed the database in Read-Only state as shown in below image :