RSS Feed

TRANSPOSE ROWS TO COLUMNS

Before SQL Server 2000, it was very difficult for developers to convert or transpose the column based values into row based values & to convert or transpose the row based values into column based values. But SQL Server 2005 & onwards made this possible.

Below is an example which will transpose values from rows to columns in SQL Server using PIVOT operator.

Transpose is required when we have to generate:
1. A single column with n rows from n columns.
2. n columns from single column with n rows.

In this post we will cover second scenario.

A simple example of transposing rows to columns:

We have a table "DailyIncome". It has 3 columns: VendorId, IncomeDay and IncomeAmount. To demonstrate PIVOT, we will cover two queries i.e.

Query (A) Find the Sum of each vendor, on day basis.
Query (B) Find the maximum income amount for each day for the requested vendor on day basis.

SELECT * FROM DailyIncome
VendorIdIncomeDayIncomeAmount
SPIKEFRI100
SPIKEMON300
FREDSSUN400
SPIKEWED500
SPIKETUE200
JOHNSWED900
SPIKEFRI100
JOHNSMON300
SPIKESUN400
JOHNSFRI300
FREDSTUE500
FREDSTUE200
SPIKEMON900
FREDSFRI900
FREDSMON500
JOHNSSUN600
SPIKEFRI300
SPIKEWED500
SPIKEFRI300
JOHNSTHU800
JOHNSSAT800
SPIKETUE100
SPIKETHU300
FREDSWED500
SPIKESAT100
FREDSSAT500
FREDTHU800
JOHNSTUE600

Query (A)

SELECT * FROM DailyIncome
PIVOT (SUM (IncomeAmount) FOR IncomeDay IN
([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS AvgIncome

VendornameMonTueWedThuFriSatSun
AMIT475150475300200100400
PANKAJ360600920800300800600
VINAY500350500800900500400

Query (B)

SELECT * FROM DailyIncome
PIVOT (MAX (IncomeAmount) FOR IncomeDay IN
([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS Maximum
WHERE VendorId IN ('SPIKE')

VendornameMonTueWedThuFriSatSun
SPIKE900200500300300100400