如何在 SQL Server 中旋轉資料表

摘要

本文說明如何旋轉 SQL Server 資料表。 假設您有一個名為 QTRSALES的資料表。 該表的資料列年份、季度及數量都有下列格式(請注意,1996的第四個季度沒有資料行): Year Quarter Amount ------------------------------- 1995 1 125,000.90 1995 2 136,000.75 1995 3 212,000.34 1995 4 328,000.82 1996 3 728,000.35 1996 2 422,000.13 1996 1 328,000.82 現在,假設您想要旋轉表格,讓您可以查看下列格式的資料: YEAR Q1 Q2 Q3 Q4 ------------------------------------------------------------------- 1995 125,000.90 136,000.75 212,000.34 328,000.82 1996 328,000.82 422,000.13 728,000.35 0.00 您要用來旋轉表格的查詢,請參閱本文的下一節。

旋轉表格的範例查詢

以下是您要用來旋轉表格的查詢:

SELECT YEAR,       Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =  Q.YEAR),0),       Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =  Q.YEAR),0),       Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =  Q.YEAR),0),       Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =  Q.YEAR),0)     FROM QTRSALES Q     GROUP BY YEAR

查詢大型表格

針對大型表格,此查詢的速度會更快:

year=q.year,SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4FROM qtrsales qGROUP BY year

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×