SQL Server PIVOT and UNPIVOT

Pivot convert row to columns and unpivot convert  column to rows. It is sometimes essential to represent data in a cross tabulation purpose, mostly used during report generation. Both static and dynamic query can do pivoting. Pivot is a relational operator to rotate data of tabular format. For example purpose ,we are creating a table and inserting data in it.

CREATE TABLE STUDENT

(

       [student_name] VARCHAR(500),

       [class] INT,

       [section] VARCHAR(5),

       [marks] INT

);

INSERT INTO STUDENT

(

        [student_name],

        [class],

        [section],

         [marks]

)

VALUES

    ('Student1 1',5, 'A', 96),

    ('Student1 2',6, 'B', 138),

    ('Student1 3',5, 'C', 37),

    ('Student1 4',7, 'A', 59),

    ('Student1 5',6, 'B', 282),

    ('Student1 6',7, 'C', 212),

    ('Student1 7',5, 'A', 78),

    ('Student1 8',8, 'B', 97),

    ('Student1 9',7, 'C', 60),

    ('Student1 10',6, 'A', 123),

    ('Student1 11',5, 'B', 220),

    ('Student1 12',8, 'A', 87);

Here is the syntax of the Pivot.

SELECT ,  
    [first pivoted column] AS ,  
    [second pivoted column] AS ,  
    ...  
    [ntht pivoted column] AS   
FROM  
    ()   
    AS   
PIVOT  
(  
    ()  
FOR   
[]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS   
; 

 
1) Specify the column to be display
2) Specify the source table, the source of the data from pivot table
3) An aggregation function is required for example AVG,Max,Min,Count,
4) Specify the value to be included in the return.

Example 1

 

select *

from

(

select class, section, marks

from STUDENT

) src

pivot

(

sum(marks)

for section in ([A], [B], [C])

) piv;

Output :

Now , we are going to see haw unpivot can be done.We are inserting all data of pivot to a table called 

"unpivot_student"

select * into unpivot_student

from

(

select class, section, marks

from STUDENT

) src

pivot

(

sum(marks)

for section in ([A], [B], [C])

) piv;

Now , see what are the data in the "unpivot_student"

select * from unpivot_student

Now , we are writing query for unpivot.

Example 2

SELECT class,

marks

FROM (SELECT *

FROM unpivot_student) p

UNPIVOT

(marks

FOR Quarter IN ( [A],[B],[C] ) ) AS unpvt

 Output :

SQL Server String function

1.Length :Len function calculate string length. It take string as argument and return numeric number.

Example

SELECT [Data]=LEN('Hellow World')

Output

12

2.Left :LEFT function return specific number of character from string from its left.It takes string as argument and integer as number of string to be return from left.

  

Example  

SELECT [Data]=LEFT ('Hellow World', 5)

Output

Hello

3.Right :Right function return specific number of character from string from its right.It takes string as argument and integer as number of string to be return from right.

  

 

Example 

SELECT [Data]=RIGHT ('Hellow World', 5)

Output

World

4.Ltrim : Ltrim function remove leading blanks from a string.It takes string as argument and return string after removing the leading blank.
 

Example 

SELECT [Data]=LTRIM(' Hellow World ')

Output

Hellow World    

5.Rtrim :  Rtrim function remove leading blanks from a string.It takes string as argument and return string after removing the leading blank.

Example 

SELECT [Data]=RTRIM(' Hellow World ')

Output

'  Hellow World '

6.Substring : Substring function returning a specific number of character from string.It takes string as argument and starting as argument and length as argument. It return string from starting position to specific number of character.

Example 

SELECT [Data]=SUBSTRING('Hellow World', 1, 5)

Output

Hello

7.Replace : Replace function replace some specific string with another string. Replace functions take string as argument, string to be replaced as another argument, string to replace as another argument. Function return string by replacingsecond string with third string.

 

Example 

SELECT [Data]=REPLACE('Hellow World','World','SQL Server')

Output

Hellow SQL Server

8.STUFF :Stuff function insert one string within another string.

Example 

SELECT [Data]=STUFF('Hellow World',7, 8, 'SQL Server')

Output

HellowSQL Server

9.UPPER : Upper function converts a string to upper-case.

Example 

SELECT [Data]=UPPER('Hellow World')

Output

HELLOW WORLD

10.LOWER :Lower function converts a string to lower-case.

Example 

SELECT [Data]=LOWER('Hellow World')

Output

hellow world

11.SPACE :SPACE function returns a string with a specified number of spaces.

Example 

SELECT [Data]='Hellow'+SPACE(10)+'World'

Output

Hellow          World

12.Replicate :Replicate is used to repeat the existing string for given number of times. 

Example 

SELECT [Data]=REPLICATE('0', 4)

Output

0000

13.CHARINDEX :CHARINDEX performs a case-insensitive search, if substring is not found within string, the CHARINDEX function will return 0.

Example 

SELECT [Data]=CHARINDEX('w', 'Hellow World')

Output

6

14.

Example 

SELECT [Data]=STR(185.476, 6, 2)

Output

185.48

15.

Example 

SELECT [Data]=QUOTENAME('Hellow World')  

Output

[Hellow World]

16.REVERSE :Reverse a string.

Example 

SELECT [Data]=REVERSE('Hellow World')

Output 

dlroW wolleH 

SQL Server Sys object Query

            Sys.Objects content list of object that has been created by user and system itself. Sys.Objects contain row for each user defined  object that has been created within a database.

             Sys.Object has several column to hold data with each as unique identification number. 

 
Below is the list of column

name : name of the object create a table in a database.
object ID : Unique Identification number of a object.
type : type of object ,it is  two character long  to define type. 

Below is the list of type.
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
P = SQL Stored Procedure
PK = PRIMARY KEY constraint
U = Table (user-defined)
V = View
UQ=Unique constraint


Now we are going to learn some  Sys.Objects query

1)Below query return all tables created by user.

SELECT * FROM sys.objects where type='U'

2)Below query return all tables created by user with name like condition.

SELECT * FROM sys.objects where type='U' and name like 'stu%'

3)Below query return all store procedure created by user.

SELECT * FROM sys.objects where type='P'

4)Below query return all View created by user.

SELECT * FROM sys.objects where type='V'

5)Below query return all primary key created by user. 
 

SELECT * FROM sys.objects where type='PK'