SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Saturday, 4 January 2014

January 04, 2014

First_Value, Last_Value and Lead function in Sql Server 2012

create table #temp1
(
userid int,
username varchar(50)
)


insert into #temp1
select 12,'siba'
union all select 13,'anurag'
union all select 13,'abhishek'
union all select 14,'ravi'
union all select 14,'anurag'
union all select 15,'siba'
union all select 19,'siba'
select 12,'tom'

select userid,username,first_value(userid) over(order by userid desc) as firstvalue,last_value(userid) over(order by userid desc) as lastvalue

 from #temp1
















-------------------------------------------------------------------------
select userid,username,first_value(userid) over(order by userid desc) as firstvalue,last_value(userid)  over(order by userid desc) as lastvalue,
lead(userid) over(order by userid asc) as leaduserid
from #temp1

















-->Last_value is actually the row currently which we are looking at and Lead is the subsequent row value




0
By at No comments:
January 04, 2014

IIF and CHOOSE in Sql Server

-->Features are avaliable in Sql Server 2012

DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;

Select iif(@X > @Y, 50, 60) As IIFResult










DECLARE @ShowIndex INT;

SET @ShowIndex =3;
Select Choose(@ShowIndex, 'A','N','U','R','A','G','N','K') As ChooseResult





0
By at No comments:
January 04, 2014

Len and Datalength in Sql server


Declare @string varchar(20)
select LEN (@string) as StringLength
set @string ='anurag '

--6(o/p) 

Declare @TraillingSpacestring varchar(40)
set @TraillingSpacestring ='anurag '
select DATALENGTH (@TraillingSpacestring) as TraillingSpacestringStringLength

--7(o/p) 


0
By at No comments:
January 04, 2014

Xml Data fetched in sql server


DECLARE @xmlDtype NVARCHAR(200)
DECLARE @h INT
SET @xmlDtype =
'<Book>
   <Id>1</Id>
   <Author>Smith</Author>
   <Title>SQL for Beginners</Title>
</Book>'

 -- Creating XML Documents
 Exec sp_xml_preparedocument @h Output, @xmlDtype
--SELECT statement using OPENXML rowset provider
SELECT * FROM OPENXML (@h, '/Book', 2)

WITH (Id VARCHAR(20), Author NVARCHAR(20), Title NVARCHAR(20))








--------------------------------------------------------------

declare @xmldata varchar(500)

set @xmldata=
'<Book>
 <Bookdetail>
  <Id>1</Id>
  <Name>Anurag</Name>
  <City>Rayagada</City>
  </Bookdetail>
 
  <Bookdetail>
  <Id>2</Id>
  <Name>Abhi</Name>
  <City>Rayagada</City>
  </Bookdetail>
</Book>'

declare @doc int

exec sp_xml_preparedocument @doc output,@xmldata

select * from openxml(@doc,'/Book/Bookdetail',2)
with
(Id varchar(2),Name varchar(10),City varchar(20))




0
By at No comments:
January 04, 2014

Use of NTILE in sql server

create table #temp1
(
userid int
)

insert into #temp1
select 12
union all select 13
union all select 13
union all select 14
union all select 14
union all select 15
union all select 19

select * from #temp1

select userid,ntile(3) over(order by userid) as final

from #temp1





















--It randomly divides

0
By at No comments:

Thursday, 26 December 2013

December 26, 2013

Grouping,Grouping Set,Grouping_Id how interesting it is




create table #temp1
(
id int,
year1 int,
count1 int
)

--drop table #temp1

insert into #temp1 values (1,2001,200)
insert into #temp1 values (1,2001,500)
insert into #temp1 values (1,2001,300)
insert into #temp1 values (1,2002,500)
insert into #temp1 values (1,2002,300)
insert into #temp1 values (1,2003,500)
insert into #temp1 values (1,2003,300)

insert into #temp1 values (2,2001,200)
insert into #temp1 values (2,2001,700)
insert into #temp1 values (2,2002,1100)
insert into #temp1 values (2,2002,200)
insert into #temp1 values (2,2002,700)
insert into #temp1 values (2,2002,1100)
insert into #temp1 values (2,2003,200)
insert into #temp1 values (2,2003,700)
insert into #temp1 values (2,2003,1100)

insert into #temp1 values (3,2001,500)
insert into #temp1 values (3,2001,1500)
insert into #temp1 values (3,2001,1000)
insert into #temp1 values (3,2001,500)
insert into #temp1 values (3,2002,1500)
insert into #temp1 values (3,2002,1000)
insert into #temp1 values (3,2003,500)
insert into #temp1 values (3,2003,1500)
insert into #temp1 values (3,2003,1000)
 -------------------------------------------------------------------------
select id,year1,sum(count1)
from #temp1
group by id,year1
with rollup
























-----------------------------------------------------------------------------

WITH CUBE

select id,year1,sum(count1)
from #temp1
group by id,year1
with cube


























-------------------------------------------------------------------------

select id,year1,sum(count1)
from #temp1
group by grouping sets((id,year1),())




















--------------------------------------------------------------------------

select
case when grouping(id)=0 then id
else 100 end as id,
year1,sum(count1)
from #temp1
group by grouping sets((id,year1),())





















--------------------------------------------------------------
select id,
case when
grouping_id(id,year1)=0
then year1
when
grouping_id(id,year1)=1
then 100
when
grouping_id(id,year1)=3
then 300 end as year1,
sum(count1),
grouping(id) as grouping_id1,
grouping(year1)as grouping_year1,
grouping_id(id,year1) as grouping_id_column
from #temp1
group by id,year1
with rollup




























0
By at No comments:

 test