SQL服务器：将行转换为列

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).

我有一个包含sales（int），month（int）列的表。我想检索每个月对应的销售总额。我需要以每个月对应的12列的形式输出，其中将有一个包含每列（月）销售额的记录。

5 个解决方案

#1

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

您应该看一下PIVOT用于切换带列的行。这可以防止每个月的select语句。像这样的东西：

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

#2

Not pretty... but this works well

不漂亮......但效果很好

SELECT
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 1) [Sales1],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 2) [Sales2],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 3) [Sales3],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 4) [Sales4],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 5) [Sales5],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 6) [Sales6],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 7) [Sales7],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 8) [Sales8],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 9) [Sales9],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 10) [Sales10],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 11) [Sales11],
    (SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 12) [Sales12]

#3

Here's an alternate way to write the pivot that gives you a little more control (especially over the column names). It's also a little easier to generate dynamic SQL for.

这是另一种编写枢轴的方法，它可以让您获得更多控制（特别是在列名称上）。生成动态SQL也更容易一些。

It's similar to Robin's answer, but has the advantage of only hitting the table once:

它类似于罗宾的答案，但其优点是只能击中桌子一次：

select
  Sales1 = sum( case when Month = 1 then Sales end )
, Sales2 = sum( case when Month = 2 then Sales end )
, Sales3 = sum( case when Month = 3 then Sales end )
-- etc..
from SalesTable;

I did some investigation, and it seems like the new pivot operator is just syntax sugar for this type of query. The query plans end up looking identical.

我做了一些调查，似乎新的pivot运算符只是这类查询的语法糖。查询计划最终看起来相同。

As an interesting aside, the unpivot operator seems to also just be syntax sugar. For example:

作为一个有趣的方面，univot运算符似乎也只是语法糖。例如：

If you have a table like:

如果你有一个像这样的表：

Create Table Sales ( JanSales int, FebSales int, MarchSales int...)

You can write:

你可以写：

 select unpivoted.monthName, unpivoted.sales
 from Sales s
 outer apply (
    select 'Jan', JanSales union all
    select 'Feb', FebSales union all
    select 'March', MarchSales
 ) unpivoted( monthName, sales );

And get the unpivoted data...

并获得不透明的数据......

#4

You can do it with OLAP. Here is another link to MSDN documentation on the topic.

你可以用OLAP来做。这是关于该主题的MSDN文档的另一个链接。

With OLAP, you can create a cube with the information you have, with the layout you need.

使用OLAP，您可以使用所需的布局创建包含所需信息的多维数据集。

If you do not want to go that way, you will have to create summary tables with .NET, Java, TransacSQL, or your preferred language to manipulate SQLServer data.

如果您不想这样，则必须使用.NET，Java，TransacSQL或首选语言创建汇总表来操作SQLServer数据。

#5

To easily transpose columns into rows with its names you should use XML. In my blog I was described this with example: Link

要轻松地将列转换为具有其名称的行，您应该使用XML。在我的博客中，我用例子描述了这个：链接

#1