Pivoting in SQL Server to Analyze Data

 

Pivoting for Data Analysis-Demo

What is Pivot?

• Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. 
• The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
• This was introduced in Sql Server in year 2005. 
• It provides an easy mechanism in Sql Server to transform rows into columns which earliear requires writing complex code.
• SQL developers use to query the data similar to pivot before pivoting introduced in SQL Server by using complex queries.
• It converts data from row level to column level. 
• It rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output

Note:  You can unpivot the data from pivot table

How Does Pivoting in SQL server looks like?

Demo:

Lets consider we have a table with below data set and i will be rotation it into 90 dgree to make a pivot

/**********SQL Code to create table and Insert data********/

Create table #query (code varchar (100),Attribute varchar (200) , value  varchar(200))

GO

Insert into #query values 

('USA'  ,       'Population'  ,     '300M'),

('USA',         'Language'   ,    'US English'),

('CANADA' ,        'Population',       '30M'),

('USA'   ,      'Capital' ,      'DC'),

('CANADA' ,        'Language'  ,     'CA English')

/******************************************/

  

Now I am looking for min popuulation and language for each code (country)

/********Query using Pivot fuction to extract data****************/

SELECT *

FROM #query AS T

PIVOT (MIN(Value) FOR Attribute 

IN ([Language],[Population],[Capital])) AS PT

/******************************************/

SQL developers use to query the data similar to pivot before pivoting introduced in SQL Server by using complex queries. So if you want to replace pivot you can try the combination of group Bu and aggregate queries like below

/********Query to Pivot the data****************/

SELECT  Code, 

        MIN(CASE WHEN Attribute = 'Language' THEN Value END) [Language],

        MIN(CASE WHEN Attribute = 'Population' THEN Value END) [Population],

        MIN(CASE WHEN Attribute = 'Capital' THEN Value END) [Capital]

FROM #query

GROUP BY Code

/******************************************/

A few examples of Pivot