Problem

You have a data in your database as below:

Sales by Branch

So now, you need to build a report Sales By Branch

Report Sales by Branch

Solution

There are many ways to implement any report like above. The most popular way is PIVOT technique in SQL Server. Ideally, PIVOT is used to convert rows to columns. To understand how it works, let see the following example.

Step 1: Create example data

— Create example data
DECLARE @Sales TABLE (BranchName nvarchar(50),YearMonth nvarchar(7),SalesAmount float)
INSERT INTO @Sales
VALUES(‘Branch A’,’2015-01′,15000),
(‘Branch A’,’2015-02′,16000),
(‘Branch A’,’2015-03′,20000),
(‘Branch A’,’2015-04′,13000),
(‘Branch A’,’2015-05′,10000),
(‘Branch A’,’2015-06′,21000),
(‘Branch A’,’2015-07′,25000),
(‘Branch A’,’2015-08′,22300),
(‘Branch A’,’2015-09′,26100),
(‘Branch A’,’2015-10′,40000),
(‘Branch A’,’2015-11′,38500),
(‘Branch A’,’2015-12′,41000),
(‘Branch B’,’2015-01′,30000),
(‘Branch B’,’2015-02′,25000),
(‘Branch B’,’2015-03′,28000),
(‘Branch B’,’2015-04′,36000),
(‘Branch B’,’2015-05′,25000),
(‘Branch B’,’2015-06′,20000),
(‘Branch B’,’2015-07′,30000)

Step 2: Understand PIVOT statement

First, we need to understand PIVOT syntax.

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>;

Now, I am be back to the example to map with PIVOT syntax. We need to indicate what columns are non-pivoted column, and [first pivoted column],[second pivoted column],…[last pivoted column]. Then <aggregation function>(<column being aggregated>)

[non-pivoted column] = BranchName

[first pivoted column] = [2015-01]

[second pivoted column] = [2015-02]

[last pivoted column] = [2015-12]

<aggregation function>(<column being aggregated>) = SUM(SalesAmount)

Step 3: So now we can implement PIVOT as below:

SELECT BranchName,[2015-01],[2015-02],[2015-03],[2015-04],[2015-05],[2015-06]
,[2015-07],[2015-08],[2015-09],[2015-10],[2015-11],[2015-12]
FROM @Sales s
PIVOT
(
SUM(SalesAmount)
FOR YearMonth IN ([2015-01],[2015-02],[2015-03],[2015-04],[2015-05],[2015-06]
,[2015-07],[2015-08],[2015-09],[2015-10],[2015-11],[2015-12])
)pv;

 

PIVOT Syntax

Run the statement to see the result, and you can use it in your report.

PIVOT final result

 

Advertisements

One thought on “TSQL: Working with PIVOT in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s