Unicque Row Num and Same Rank in Sql server

 Use Of  Row_Number() ,Rank() function , Dense_Rank()

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;    

After executing it, we will get:

 Col_Value  Row ID
           A     1
           A     2
           A     3
           B     4
           B     5
           C     6
           C     7
As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

After executing it, we will get:

Col_Value Row ID
     A    1
     A    1
     A    1
     B    4
     B    4
     C    6
     C    6 

As we can see,rowid is unique for each distinct value, but with a gap. What is this gap?
This gap represents number of occurrence. For example: value a is repeated thrice and has rank 1, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank()

 his function is similar to Rank with only difference, this will not leave gaps between groups.
 So if we use the same query used for Rank, then:

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

 After executing it, we will get:

Col_Value Row ID
        A     1
        A     1
        A     1
        B     2
        B     2
        C     3
        C     3

	So it is clear that it generates a unique id for each group and without repetition. As we are clear now what these functions do, we can use them in different scenarios based our need. For example: Row_Number() can be used if we want to skip and fetch some records. Take a look at this query:
	Similarly, we can use Dense_Rank()when we need to calculate  row id with Select Distinct.
	SELECT DISTINCT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID' FROM @Table; Will return:-
	Col_Value Row ID         A     1         B     2         C     3

Read More

COALESCE function

Concatenate many rows into a single text string using SQL Server
Using  COALESCE function-

 What is COALESCE function-
Concatenate many rows into a single text string with comma separated using SQL Server COALESCE   function.

My table format is like this:
 (Select Name from Tbl_Customer)

Name
Jeet
Nitin
Pulkit
Rahul
Vibha

 I need output like this:
     Name
1. Jeet ,Nitin,Rahul,Pulkit,Vibha

Use This Code 
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name(ColumnName)
FROM  Tbl_Customer
select @Names

Read More

Rank Given In Table

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of  ROW_NUMBER() Function 
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.


DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id  CityName   C_Id
1    Allahabad    1   
2    Allahabad   1
3    Allahabad    1   
4    Patna        1   
5    Patna        2   
6    Patna        3   
7    Varanasi    3   
8    Varanasi    3   
9    Varanasi    3   
10    Varanasi    3    

Give Rank 

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName C_Id RowNo SameRank
1    Allahabad    1    1      1
2    Allahabad    1    2      1
3    Allahabad    1    3      1
4    Patna           1    2      2
5    Patna           2    2      2
6    Patna           3     2      2
7    Varanasi      3     1      3
8    Varanasi      3     2      3
9    Varanasi      3     3      3
10    Varanasi    3    4       3

Read More

Create Table

Create Table In Sql Server

Syntax-

CREATE TABLE TableName
(
ColumnName1  INT PRIMARY KEY IDENTITY,
ColumnName2,
ColumnName3
)

Ex.
 CREATE TABLE Tbl_CityMaster
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)

Read More

Show Table Data

This IS Code For Show  Table Data..
If We Want to Get Data Inserted in Any  Table And Copy then use  this Code in SQL Server

Syntax-
DECLARE @Fields VARCHAR(max); SET @Fields = '[ColumnName1], [ColumnName2], [ColumnName3]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Table Name'   -- your Table Name
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)

 Ex.-
DECLARE @Fields VARCHAR(max); SET @Fields = '[id], [Waiver], [isactive]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'plot_Waiver'               -- your table
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)
OutPut-

INSERT INTO plot_Waiver([id], [Waiver], [isactive])
SELECT '1', '40.00', '1' UNION
SELECT '2', '25.00', '1' UNION
SELECT '4', '20.00', '1' UNION
SELECT '5', '45.00', '1'

Read More

Use SQL Server Functions

SQL Aggregate Functions
This functions return a single value, calculated from values in a column.
Like Ex.-

AVG() - Returns the average value.
      Syntax - SELECT AVG(column_name) FROM table_name
COUNT() - Returns the number of rows.
     Syntax -  SELECT COUNT(column_name) FROM table_name
FIRST() - Returns the first value.
    Syntax - SELECT FIRST(column_name) FROM table_name
LAST() - Returns the last value.
    Syntax - SELECT LAST(column_name) FROM table_name
MAX() - Returns the largest value.
    Syntax - SELECT MAX(column_name) FROM table_name
MIN() - Returns the smallest value.
   Syntax - SELECT MIN(column_name) FROM table_name
SUM() - Returns the sum.
    Syntax - SELECT SUM(column_name) FROM table_name

SQL Scalar functions
This Functions return a single value, based on the input value.
Ex.

  UCASE() - Converts a field to upper case
        Syntax - SELECT UCASE(column_name) FROM table_name;
   LCASE() - Converts a field to lower case
        Syntax - SELECT UCASE(column_name) FROM table_name;
   MID() - Extract characters from a text field.
        Syntax - SELECT MID(column_name,start,length) AS some_name FROM table_name;
   LEN() - Returns the length of a text field
       Syntax - SELECT LEN(column_name) FROM table_name;
   ROUND() - Rounds a numeric field to the number of decimals specified
         Syntax - SELECT ROUND(column_name,decimals) FROM table_name;
   NOW() - Returns the current system date and time
        Syntax - SELECT NOW() FROM table_name;
    FORMAT() - Formats how a field is to be displayed
        Syntax - SELECT FORMAT(column_name,format) FROM table_name;

Read More

CSS 3D Transforms

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

    <title></title>

    <style type="text/css">

     .container{

/* How pronounced should the 3D effects be */

perspective:800px;

-webkit-perspective:800px;

background: radial-gradient(#e0e0e0, #aaa);

width:350px;

height:380px;

margin:0 auto;

border-radius:6px;

position:relative;

}

.iphone-front,

.iphone-back{

/* Enable 3D transforms */

transform-style: preserve-3d;

-webkit-transform-style: preserve-3d;

/* We are using two separate divs for the front and back of the

  phone. This will hide the divs when they are flipped, so that the

  opposite side can be seen:  */

backface-visibility: hidden;

-webkit-backface-visibility: hidden;

width:200px;

height:333px;

position:absolute;

top:50%;

left:50%;

margin:-166px 0 0 -100px;

background:url(http://demo.tutorialzine.com/2013/10/css3-features-you-can-finally-use/assets/img/iphone.png) no-repeat left center;

/* Animate the transitions */

/*transition:0.8s;*/

    transition:1.5s;

}

.iphone-back{

/* The back side is flipped 180 deg by default */

transform:rotateY(180deg);

-webkit-transform:rotateY(180deg);

background-position:right center;

}

.container:hover .iphone-front{

/* When the container is hovered, flip the front side and hide it .. */

transform:rotateY(180deg);

-webkit-transform:rotateY(180deg);

}

.container:hover .iphone-back{

/* .. at the same time flip the back side into visibility */

transform:rotateY(360deg);

-webkit-transform:rotateY(360deg);

}

    </style>

</head>

<body>

   <div class="container">

<div class="iphone-front"></div>

<div class="iphone-back"></div>

     <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDx4ENlGMpgGklms8BkkVYgSBh7vJj0JbauaFHtG9o3t_c0nK6aqy9G7X0Tl1tLMhYdf0G7_Oa8p5snYdEPOT0tFRFkAUMEcvmBl9AnX0Xwk2low6B5nkuyvOCwNphrjD-SaphfYZsezby/s1600-r/finalc%2523logoPNGupdated.png" style="height:50px; width:100px;" /> 

</div>

</body>

</html>

Read More