SQL 服务器:将行转换为列 [英] SQL server: convert rows to columns

问题描述

我有一个包含 sales(int)、month(int) 列的表格.我想检索每个月对应的销售额总和.我需要以 12 列的形式输出，对应于每个月，其中将有一条记录包含每列(月)的销售额.

I have a table with columns sales(int), month(int). I want to retrieve sum of sales corresponding to every month. I need ouput in form of 12 columns corresponding to each month in which there will be a single record containing sales for for each column(month).

推荐答案

你应该看看 PIVOT 用于切换行与列.这可以防止每个月的 select 语句.像这样:

You should take a look at PIVOT for switching rows with columns. This prevents a select statement for each month. Something like this:

DECLARE @salesTable TABLE
(
    [month] INT,
    sales INT
)

-- Note that I use SQL Server 2008 INSERT syntax here for inserting
-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
      ,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
      ,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
      ,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)

SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM
(
    SELECT [month], sales
    FROM @salesTable
) AS SourceTable
PIVOT
(
    SUM(sales)
    FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

这篇关于SQL 服务器:将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！