Once upon a time, I’m curious to know how to transpose table in SQL Server. Finally I found interesting clause called by PIVOT & UNPIVOT. For example we have data such below:
|
Category |
Amount |
| Muhammad |
1 |
| Muhammad |
2 |
| Muhammad |
3 |
| Arif |
4 |
| Arif |
5 |
And we want to make a summary sum of amount of each category to be one line. You can use PIVOT to solve this.
Please execute below query:
[code language=”sql” title=”Sample PIVOT”]
SELECT ‘Sample Pivot’ AS a, [muhammad], [arif]
INTO #temp
FROM
(
SELECT ‘muhammad’ AS category, 1 AS amount
UNION ALL
SELECT ‘muhammad’ AS category, 2 AS amount
UNION ALL
SELECT ‘muhammad’ AS category, 3 AS amount
UNION ALL
SELECT ‘arif’ AS category, 4 AS amount
UNION ALL
SELECT ‘arif’ AS category, 5 AS amount
) AS tbl
PIVOT (
SUM(tbl.amount) FOR tbl.category IN ([muhammad], [arif])
) pvt
SELECT * FROM #temp
[/code]
You will get a result like this:
|
category |
Muhammad |
Arif |
|
Sample Pivot |
6 |
9 |
You can use UNPIVOT to reverse above result with this query (I assume query result above you put inside temporary table with name #temp):
[code language=”sql” title=”Sample UNPIVOT”]
SELECT category, amount
FROM
(
SELECT muhammad, arif FROM #temp
) tbl
UNPIVOT
(
amount FOR category IN ([muhammad], [arif])
) pvt
[/code]
You will get result like basic data.
I hope it help.
Marifnst, 2013-06-11
Leave a Reply