Pivot operator in sql server - Part 54

Suggested SQL Server Videos:
Part 11 - Group By
Part 48 - Derived table and CTE in sql server


One of my youtube channel subscribers, has asked me to make a video on PIVOT operator. So here we are with another sql server video.

Pivot is a sql server operator that can be used to turn unique values from one column, into multiple columns in the output, there by effectively rotating a table.



Let's understand the power of PIVOT operator with an example
Create Table tblProductSales
(
 SalesAgent nvarchar(50),
 SalesCountry nvarchar(50),
 SalesAmount int
)


Insert into tblProductSales values('Tom', 'UK', 200)
Insert into tblProductSales values('John', 'US', 180)
Insert into tblProductSales values('John', 'UK', 260)
Insert into tblProductSales values('David', 'India', 450)
Insert into tblProductSales values('Tom', 'India', 350)
Insert into tblProductSales values('David', 'US', 200)
Insert into tblProductSales values('Tom', 'US', 130)
Insert into tblProductSales values('John', 'India', 540)
Insert into tblProductSales values('John', 'UK', 120)
Insert into tblProductSales values('David', 'UK', 220)
Insert into tblProductSales values('John', 'UK', 420)
Insert into tblProductSales values('David', 'US', 320)
Insert into tblProductSales values('Tom', 'US', 340)
Insert into tblProductSales values('Tom', 'UK', 660)
Insert into tblProductSales values('John', 'India', 430)
Insert into tblProductSales values('David', 'India', 230)
Insert into tblProductSales values('David', 'India', 280)
Insert into tblProductSales values('Tom', 'UK', 480)
Insert into tblProductSales values('John', 'US', 360)
Insert into tblProductSales values('David', 'UK', 140)




Select * from tblProductSales: As you can see, we have 3 sales agents selling in 3 countries
Pivot Table Source

Now, let's write a query which returns TOTAL SALES, grouped by SALESCOUNTRY and SALESAGENT. The output should be as shown below.
Group By Output

A simple GROUP BY query can produce this output.
Select SalesCountry, SalesAgent, SUM(SalesAmount) as Total
from tblProductSales
group by SalesCountry, SalesAgent
order by SalesCountry, SalesAgent


At, this point, let's try to present the same data in different format
using PIVOT operator.

Pivot operator output

Query using PIVOT operator:
Select SalesAgent, India, US, UK
from tblProductSales
Pivot
(

   Sum(SalesAmount) for SalesCountry in ([India],[US],[UK])
) as PivotTable


This PIVOT query is converting the unique column values
(India, US, UK) in SALESCOUNTRY column, into Columns in the output, along with performing
aggregations on the SALESAMOUNT column. The Outer query, simply, selects SALESAGENT column from tblProductSales table, along with pivoted columns from the PivotTable.

Having understood the basics of PIVOT, let's look at another example. Let's create tblProductsSale, a slight variation of tblProductSales, that we have already created. The table, that we are creating now, has got an additional Id column.
Create Table tblProductsSale
(
   Id int primary key,
   SalesAgent nvarchar(50),
   SalesCountry nvarchar(50),
   SalesAmount int
)


Insert into tblProductsSale values(1, 'Tom', 'UK', 200)
Insert into tblProductsSale values(2, 'John', 'US', 180)
Insert into tblProductsSale values(3, 'John', 'UK', 260)
Insert into tblProductsSale values(4, 'David', 'India', 450)
Insert into tblProductsSale values(5, 'Tom', 'India', 350)
Insert into tblProductsSale values(6, 'David', 'US', 200)
Insert into tblProductsSale values(7, 'Tom', 'US', 130)
Insert into tblProductsSale values(8, 'John', 'India', 540)
Insert into tblProductsSale values(9, 'John', 'UK', 120)
Insert into tblProductsSale values(10, 'David', 'UK', 220)
Insert into tblProductsSale values(11, 'John', 'UK', 420)
Insert into tblProductsSale values(12, 'David', 'US', 320)
Insert into tblProductsSale values(13, 'Tom', 'US', 340)
Insert into tblProductsSale values(14, 'Tom', 'UK', 660)
Insert into tblProductsSale values(15, 'John', 'India', 430)
Insert into tblProductsSale values(16, 'David', 'India', 230)
Insert into tblProductsSale values(17, 'David', 'India', 280)
Insert into tblProductsSale values(18, 'Tom', 'UK', 480)
Insert into tblProductsSale values(19, 'John', 'US', 360)
Insert into tblProductsSale values(20, 'David', 'UK', 140)


Now, run the same PIVOT query that we have already created, just by changing the name of the table to tblProductsSale instead of tblProductSales
Select SalesAgent, India, US, UK

from tblProductsSale
Pivot

(
   Sum(SalesAmount) for SalesCountry in ([India],[US],[UK])
)
as PivotTable


This output is not what we have expected.

Unexpected Pivot Table Output

This is because of the presence of Id column in tblProductsSale, which is also considered when performing pivoting and group by. To eliminate this from the calculations, we have used derived table, which only selects, SALESAGENT, SALESCOUNTRY, and SALESAMOUNT. The rest of the query is very similar to what we have already seen.
Select SalesAgent, India, US, UK
from
(

   Select SalesAgent, SalesCountry, SalesAmount from tblProductsSale
) as SourceTable
Pivot
(
 Sum(SalesAmount) for SalesCountry in (India, US, UK)
) as PivotTable


UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The syntax of PIVOT operator from MSDN
SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,

    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
    [<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column], ... [last pivoted column])
)
AS <alias for the pivot table>
<optional ORDER BY clause>;


7 comments:

  1. Hi Venkat sir, ur videos are awesome and these are very helpful to us and i got lot of knowledge from these videos.

    One request sir Please upload unpivot operator with example.

    Thanks you.

    ReplyDelete
  2. Select SalesAgent, India, US, UK
    from tblProductsSale
    Pivot
    (
    Sum(SalesAmount) for SalesCountry in ([India],[US],[UK])
    )
    as PivotTable

    This Cause "Sum(SalesAmount) for SalesCountry in ([India],[US],[UK])" India,US,UK Uhave Enter Manually, But How to Seat Automatic
    if Have any Select Quay ,pls Explane

    ReplyDelete
  3. you can store your calumns in local variable and then pass to IN
    then it will work as Dynamic Pivot Query

    ReplyDelete
  4. select SalesCountry , David , Jhon, Tom
    from
    (
    select SalesCountry,SalesAgent ,SalesAmount from tblProductsSale
    ) as sourcetable
    pivot
    (
    sum ( salesAmount)
    for salesAgent
    in ([David],[Jhon],[Tom])
    ) as pviotTable
    -- this qurey is giving Null in only Jhon column
    david jhon Tom
    India 960 NULL 350
    UK 360 NULL 1340
    US 520 NULL 470

    ReplyDelete
  5. sorry got an error in john's spell :P

    ReplyDelete
  6. Dynamic pivot:

    DECLARE @COLS NVARCHAR(2000)
    DECLARE @QUERY NVARCHAR(4000)

    SELECT @COLS = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + SalesCountry
    FROM tblProductsSale
    FOR XML PATH ('')),1,2,'') + ']'

    --SELECT @COLS

    SET @QUERY = 'SELECT SalesAgent, '+ @COLS + 'FROM
    (SELECT SalesAgent,SalesCountry,SalesAmount
    FROM tblProductsSale) AS stab
    PIVOT ( SUM(SalesAmount) FOR SalesCountry IN ('+@COLS+')) AS PivotTable;'

    EXECUTE (@QUERY);

    ReplyDelete
  7. my upibot query is not running , facing errors again n again

    SELECT SalesAgent, Country, SalesAmount
    FROM tblProductSales
    UNPIVOT
    (
    SalesAmount
    FOR Country IN (India, US ,UK)
    ) AS UnpivotExample


    error

    Msg 207, Level 16, State 1, Line 72
    Invalid column name 'India'.
    Msg 207, Level 16, State 1, Line 72
    Invalid column name 'US'.
    Msg 207, Level 16, State 1, Line 72
    Invalid column name 'UK'.
    Msg 265, Level 16, State 1, Line 71
    The column name "SalesAmount" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
    Msg 8156, Level 16, State 1, Line 73
    The column 'SalesAmount' was specified multiple times for 'UnpivotExample'.

    can u help

    ReplyDelete

It would be great if you can help share these free resources