Swim in SQL server

Archive for December, 2011

Working with Window Functions in SQL Server

There are 4 Types of Rank functions in SQL Server.
Row_Number() : Row Number will return serial numbers in the result set.
Rank()                   : Rank will returns the rank of each row , if the values are same for more records it will give the same number,if the value is change, then it will give the row number in the partition.
Dense_Rank()    : Dense_Rank will returns the rank of each row,if the values are same, then it will give the same number,if the value is changed ,then it will give the next number of the previous row in the partition.
Ntile(n)                : It will partition the result set into “n” numbers and give the rank to each record.
–Declare a table with name of @CLASSROOM
DECLARE @CLASSROOM TABLE (ID INT IDENTITY, NAME VARCHAR(100) , BRANCH VARCHAR(10) , M1 INT , M2 INT , M3 INT , TOT as (M1+M2+M3) )
–Insert values into @CLASSROOM
INSERT INTO @CLASSROOM
VALUES ( ‘RAJ’ ,‘CSE’ , 60 , 70 , 40 )
              ,( ‘RAHUL’ ,‘CSE’ , 60 , 80 , 30 )
              ,( ‘RAMESH’ ,‘CSE’ , 60 , 60 , 40 )
              ,( ‘RAKESH’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAM’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAVI’ ,‘IT’ , 60 , 70 , 90 )
              ,( ‘RAJESH’ ,‘IT’ , 70 , 30 , 80 )
              ,( ‘ROHITH’ ,‘CSE’ , 50 , 60 , 90 )
              ,( ‘ROCK’ ,‘IT’ , 50 , 70 , 50 )
              ,( ‘RAAGHAV’ ,‘CSE’ , 50 , 60 , 60 )
 
–Ranking functions
SELECT NAME , TOT
              ,ROW_NUMBER() OVER(ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER( ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM
 
–Ranking functions With Partition By clause.
SELECT BRANCH,NAME , TOT
              ,ROW_NUMBER() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM

How to separate Integers from String using T-SQL

Here we can see different methods to separate Integers and String data types from a string variable.

SET NOCOUNT ON

declare @str varchar(100) = 'A1B2C3D4'
DECLARE @I INT = 1,@var varchar(100)='' , @int Varchar(100) = ''

WHILE @I <= LEN(@str)
begin
-- 48 Is ASCII Key for '0' and 57 is for '9'
--Checking Each char is between 0 to 9 or not. If yes then concatenation to @int variable. Else concatenation with @var Variable.
IF @I = 1 SELECT @str 'Input String' , SUBSTRING(@STR,@I,1) 'First Char of the Input String', ASCII(SUBSTRING(@STR,@I,1))  AS 'ASCII Value of the First Char'

IF ASCII(SUBSTRING(@STR,@I,1)) BETWEEN 48 AND 57
--IF PATINDEX('[^0-9-]',SUBSTRING(@STR,@I,1) ) = 0
BEGIN
IF @I = 1 SELECT @str , SUBSTRING(@STR,@I,1), ASCII(SUBSTRING(@STR,@I,1))
SET @int += SUBSTRING(@STR,@I,1)+','
END
ELSE
BEGIN
SET @var += SUBSTRING(@STR,@I,1)+','
END
set @I +=1
end
SELECT @str 'Input String','Integers'= SUBSTRING(@int,1,LEN(@int)-1) , REVERSE(STUFF(REVERSE(@var),1,1,'')) AS [String]
---------------------------Method 2----------------------------
/*
Inserting each character into @TBL table.
Using with CASE Statement displaying and Checking CHAR is Integer or String.
*/
SELECT @I = LEN(@Str)
DECLARE @TBL TABLE (ID INT IDENTITY , [Char] CHAR(1))
WHILE @I >= 1
begin
INSERT INTO @TBL SELECT SUBSTRING(@STR,@I,1)
set @I -=1
end

SELECT [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integer' ELSE 'Strings' END AS [TYPE]
FROM @TBL
ORDER BY    [TYPE], ID  DESC
--------------------------------Method 3-------------------
SELECT Integers , Strings  FROM
(
SELECT ID, [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integers' ELSE 'Strings' END AS [TYPE]
FROM @TBL
)  AS P
PIVOT(MAX(CHAR) FOR [TYPE] IN (Integers,Strings)  ) AS PVT
ORDER BY Integers  , Strings

Inserting default values in SQL-Table

How to insert Identity value in a table?
/*
In the below example we have single Identity column table. Using with "Default Values" we can insert the values into table.
*/
create table #tbl(id int identity)
go

insert into #tbl default values
go 10

select * from #tbl
go

drop table #tbl
go

____________________________________________________________________________________________
CREATE TABLE #TBL1(ID INT IDENTITY , NAME VARCHAR(10) NULL , COUNTRY VARCHAR(10) DEFAULT 'INDIA')
go

INSERT INTO #TBL1 DEFAULT VALUES
GO 10

SELECT * FROM #TBL1
go

drop table #TBL1
go

/*
Here the ID is Identity column , Name will allow the nulls and Country Default value is 'India'.
*/

3 Ways to give alias names in select statement.

3 Ways to give alias names in select statement.

SELECT TABLE_NAME=name,[Table ID]= OBJECT_ID , ‘Table Type’=type FROM sys.tables

SELECT name TABLE_NAME, OBJECT_ID [Table ID], type ‘Table Type’ FROM sys.tables

SELECT name AS TABLE_NAME, OBJECT_ID AS [Table ID], type AS ‘Table Type’ FROM sys.tables

Case Statement in SQL Srever

How to use Case Statement in SQL Server? Here i explained Case statement with simple exampleS.

CREATE TABLE #Student(Stud_ID INT IDENTITY, NAME VARCHAR(100), DeptID INT, DOB DATETIME,Gender CHAR(1),JoiningDate Datetime,Marks INT)

INSERT INTO #Student

VALUES (‘ABHISHEK’ , 1 , ’01-JAN-1986′,1, ’01-JAN-2011′,50),

(‘PRASHANTH’, 1 ,’01-JUN-1985′,1,’01-JUN-2010′,70),

(‘RAMS’, 2 ,’01-MAR-1986′,1,’01-MAR-2011′,60),

(‘SRIRAM’, 3 ,’01-JAN-1985′,1 ,’01-JAN-2010′,50),

(‘RAM’, 1 ,’01-JAN-1986′,1,’01-JAN-2011′,20),

(‘ABHI’, 2 ,’01-JAN-1986′,1,’01-JAN-2011′,70),

(‘RANI’, 1 ,’01-JAN-1986′,2,’01-JAN-2010′,80),

(‘JYOTHI’,3 ,’01-JAN-1985′,2,’01-JAN-2011′,90),

(‘PRIYA’, 2 ,’01-JAN-1986′,2,’01-JAN-2011′,34),

(‘ROJA’, 1 ,’01-JAN-1985′,2,’01-JAN-2010′,50)

SELECT*FROM #Student

################# Example 1 #################

/*USING CASE STATMENT IN SELECT

HERE I WANT TO DISPLAY THE GENDER(M,F) ACCORDING TO GENDER COLUMN,IF GENDER=1 THEN M, GENDER= 2 THEN F

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks,S.JoiningDate

FROM #Student S

/*

Here we are cheking condition based on only single Column(Gender) with equals operator.

So I have Written “S.Gender” After “CASE” Statment.

*/

/*

################# Example 2 #################

Here we can know how to use Conditional Operators in Case Statment.

Using Case Statment we can Give Grade of students.

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks

,CASE WHEN S.Marks <= 34 THEN ‘FAIL’

WHEN S.Marks >= 35 AND S.Marks <= 59 THEN ‘3rd Class’

WHEN S.Marks >= 60 AND S.Marks <= 74 THEN ‘2nd Class’

WHEN S.Marks >= 75 AND S.Marks <= 100 THEN ‘1st Class’

END AS Grade

,S.JoiningDate

FROM #Student S

ORDER BY Grade , S.NAME

/*

Here am going to show how to use CASE Statment in WHERE Clause.

################ Example 3 ##########################################

*/

SELECT*

FROM #Student S

WHERE (CASE S.DeptID WHEN 1 THEN’CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END ) = ‘CSE’

/*

Here am going to show, How to use CASE Statment in ORDER BY Clause.

###################### Example 4 ####################

As per my requirement, The first record should be Fixed Value ,

Like In my below example,

If i write Order by Branch, then i will get the output order as CSE , CSIT , ECE .

If i choose Desc Order then output order is ECE,CSIT,CSE.

I want to display CSIT Records first and other branches in desc order.

*/

SELECT S.Stud_ID , NAME , CASE S.DeptID WHEN 1 THEN ‘CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END AS Branch

,S.Gender,S.JoiningDate

INTO #Tmp_Student

FROM #Student S

SELECT*

FROM #Tmp_Student

ORDER BY CASE Branch WHEN’CSIT’THEN 0 ELSE 1 END,Branch desc, NAME

/*Using with Case Statment , we can Generate CROSS TAB(Crystal Reports)/Matrix (SSRS) Type output.*/

SELECT Branch ,

COUNT(CASE WHEN YEAR(JoiningDate)=2010 THEN Stud_ID ELSE NULL END) AS [2010]

,COUNT(CASE WHEN YEAR(JoiningDate)=2011 THEN Stud_ID ELSE NULL END) AS [2011]

,COUNT(CASE WHEN YEAR(JoiningDate)=2012 THEN Stud_ID ELSE NULL END) AS [2012]

FROM #Tmp_Student

group by Branch

/*

We Can use Case statment in UPDATE Statment also.

Here is the Example.

*/

UPDATE TS SET TS.Gender =(CASE TS.Gender WHEN 1 THEN’M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END)

FROM #Tmp_Student TS

SELECT*FROM #Tmp_Student

/* Clear Temp Memory */

DROPTABLE #Student , #Tmp_Student