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

VendorId	IncomeDay	IncomeAmount
SPIKE	FRI	100
SPIKE	MON	300
FREDS	SUN	400
SPIKE	WED	500
SPIKE	TUE	200
JOHNS	WED	900
SPIKE	FRI	100
JOHNS	MON	300
SPIKE	SUN	400
JOHNS	FRI	300
FREDS	TUE	500
FREDS	TUE	200
SPIKE	MON	900
FREDS	FRI	900
FREDS	MON	500
JOHNS	SUN	600
SPIKE	FRI	300
SPIKE	WED	500
SPIKE	FRI	300
JOHNS	THU	800
JOHNS	SAT	800
SPIKE	TUE	100
SPIKE	THU	300
FREDS	WED	500
SPIKE	SAT	100
FREDS	SAT	500
FRED	THU	800
JOHNS	TUE	600

Query (A)

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

Vendorname	Mon	Tue	Wed	Thu	Fri	Sat	Sun
AMIT	475	150	475	300	200	100	400
PANKAJ	360	600	920	800	300	800	600
VINAY	500	350	500	800	900	500	400

Query (B)

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

Vendorname	Mon	Tue	Wed	Thu	Fri	Sat	Sun
SPIKE	900	200	500	300	300	100	400