PIVOT SQL Server Example

SQL Server  > Operators > PIVOT

PIVOT rotates a values from a table by turning the unique values from one column into multiple columns in the output (cross-tab). Also performs aggregations where they are required.

Example:

1.Using PIVOT to get number of product sales

create table #Sales(ProductId int, [Year] int, Country varchar(100))

insert into #Sales(ProductId, [Year], Country  ) values  (1,2012,'Germany'), (1,2012,'UK') , (1,2013,'France') , (2,2013,'Germany')  , (2,2013,'Germany')  , (2,2013,'UK')

SELECT

       Country, [2012], [2013]
FROM
       (SELECT ProductId, [Year], Country FROM #Sales) p
       PIVOT
       (
              COUNT (ProductId)
              FOR [Year] IN ( [2012], [2013] )
       ) AS pvt
ORDER BY pvt.Country ;

drop table #Sales

Result:



2. Dynamic PIVOT and cross-tab query

Create Local temporary table in SQL Server

SQL Server  > Temporary table > Create

Use # character in front of table name to create local temporary table

Example: Create temporary table #user

create table #user(id int, superiorid int)
insert into #user(id, superiorid)
values (1,null), (2,1) , (3,1)
drop table #user

Visual Basic run-time member functions

VB.NET  > Run-time member functions

AppActivate	Asc	AscW	Beep
CallByName	CBool	CByte	CChar
CDate	CDbl	CDec	ChDir
ChDrive	Choose	Chr	ChrW
CInt	CLng	CObj	Command
CreateObject	CShort	CSng	CStr
CType	CurDir

DateAdd	DateDiff	DatePart	DateSerial
DateValue	Day	DDB	DeleteSetting
Derived Math	Dir	Environ	EOF
ErrorToString	FileAttr	FileClose	FileCopy
FileDateTime	FileGet	FileGetObject	FileLen
FileOpen	FilePut	FilePutObject	FileWidth
Filter	Fix	Format	FormatCurrency
FormatDateTime	FormatNumber	FormatPercent	FreeFile
FV	GetAllSettings	GetAttr	GetChar
GetException	GetObject	GetSetting

What is Visual Basic .NET

Visual Basic .NET is the result of a significant rebuild of Visual Basic for the Microsoft .NET Framework.
Visual Basic .NET gives you access to system resources that in the past required the use of languages like C++. One of the most important additions is object inheritance.

• System 
• Run time functions
• Object-Oriented Programming
• Form
• Statements

RANK and DENSE_RANK SQL Server

SQL Server > Built-in Functions > RANK & DENSE_RANK

RANK returns the rank of each row within the partition of a result set

DENSE_RANK returns the rank of rows within the partition of a result set  without any gaps in the ranking

                    OVER ( [ PARTION BY ] [ORDER BY]) 
Example:

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (3,'p3', 100,1) ,   (2,'p4', 50,2)

 

--see image 1

select * from #LocalTempTable

--see image 2

SELECT i.*

    ,RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 3

SELECT i.*

    ,RANK() OVER
    (PARTITION BY i.DeptId  ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 4

SELECT i.*

    ,DENSE_RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

drop table #LocalTempTable

Results:

 

Image 1 Salary Data

Image 2 RANK without PARTITION BY will rank all rows. Note the gap between 2 and 4

Image 3 RANK with PARTITION BY will rank rows in the same DeptID.

Image 2 DENSE_RANK  Note there are not gaps

 

 

 



 



MIN function SQL Server Example

SQL Server > Built-in Functions > MIN

MIN returns the minimum value

Warning! Null values are ignored!

Example

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p3', null,2) ,   (2,'p4', 50,2)

select * from #LocalTempTable

select min(Salary) MIN_salary from #LocalTempTable
select min(Salary) MIN_salary, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Results:

ID Name Salary DeptId
1 p1 100 1
2 p2 200 1
2 p3 NULL 2
2 p4 50 2

MIN_salary
50

MIN_salary DeptId
100 1
50 2

AVG function SQL Server Example

SQL Server > Built-in Functions > AVG

AVG returns the average of the values in a group without Null values

Example:

1. Calculate average salary global and for each department

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p2', null,2)

select avg(Salary) avg_salary from #LocalTempTable

select avg(Salary) avg_salary, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Results:

avg_salary
150

avg_salary DeptId
150            1
NULL       2

2. Average without zero (0)

UPPER SQL Server Example

SQL Server > Built-in Functions > UPPER

UPPER  converts a character expression to uppercase.

Example

SELECT UPPER('this is lowercase expression')
Result:
THIS IS LOWERCASE EXPRESSION