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 |
No comments:
Post a Comment