SS090804 - การใช้ PIVOT

Modified on 2010/01/13 15:10 by ninefyi — Categorized as: SQL Server

วันนี้มานำเสนอวิธีการแปลงข้อมูลจากแนวนอน เป็นแนวตั้ง เพื่อหาค่าผลรวม อธิบายไม่ค่อยเก่งลองดูตาม code กันดูนะครับ

CREATE TABLE table1 (
	report_id INT
	, dy DATETIME
	, qty INT
)

INSERT INTO table1 (report_id, dy, qty) 

SELECT 1, '2008/01/02', 2
UNION ALL
SELECT 2, '2008/09/02', 1
UNION ALL
SELECT 3, '2008/02/02', 3
UNION ALL
SELECT 1, '2008/03/02', 4
UNION ALL
SELECT 2, '2008/09/02', 5
UNION ALL
SELECT 3, '2008/04/02', 1
UNION ALL
SELECT 1, '2008/09/02', 1
UNION ALL
SELECT 2, '2008/01/02', 1
UNION ALL
SELECT 3, '2008/05/02', 1
UNION ALL
SELECT 1, '2008/10/02', 9
UNION ALL
SELECT 2, '2008/11/02', 1
UNION ALL
SELECT 3, '2008/01/02', 1
UNION ALL
SELECT 1, '2008/11/02', 1

SELECT report_id
	, ISNULL([1], 0) AS 'Jan'
	, ISNULL([2], 0) AS 'Feb'
	, ISNULL([3], 0) AS 'Mar'
	, ISNULL([4], 0) AS 'Apr'
	, ISNULL([5], 0) AS 'May'
	, ISNULL([6], 0) AS 'Jun'
	, ISNULL([7], 0) AS 'Jul'
	, ISNULL([8], 0) AS 'Aug'
	, ISNULL([9], 0) AS 'Sep'
	, ISNULL([10], 0) AS 'Oct'
	, ISNULL([11], 0) AS 'Nov'
	, ISNULL([12], 0) AS 'Dec'
FROM (
		SELECT report_id
			, MONTH(dy) AS mm
			, qty
		FROM table1
	 ) ps 
PIVOT
(
	SUM(qty) FOR mm IN( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS pvt

DROP TABLE table1