Pivot table in sql

Simple Way To Use Pivot In SQL Query
Maksud Saifullah Pulak, 4 Dec 2015 CPOL

    4.70 (75 votes)
Rate this:
vote 1vote 2vote 3vote 4vote 5
Transforming data from row-level data to columnar data.
Introduction
This is a very simple example of Pivot query for the beginners. We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

Background
This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.

Using the Code
Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate, InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum of InvoiceAmount each month.

Hide   Copy Code
SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult

Hide   Copy Code
SELECT *
FROM (
    SELECT
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
        InvoiceAmount as Amount
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)