将一列中的多行数据表示为一行SQL Server中的多列

问题描述

我有一个具有以下结构和数据的表:

I have a table with the following structure and data:

batsman | runs | year

 1      | 800  | 2012
 1      | 950  | 2011
 1      | 1050 | 2010
 2      | 550  | 2012
 2      | 650  | 2011
 2      | 400  | 2010
 3      | 900  | 2012

此数据需要通过sql查询选择为:

This data needs to be Selected through a sql query as:

batsman | 2012 | 2011 | 2010

  1     | 800  | 950  | 1050
  2     | 550  | 650  | 400
  3     | 900  |  -   |  -

我正在尝试通过存储的proc来做到这一点.可以假定列数(以年为单位)是固定的:3.还要注意，没有必要进行算术运算-我需要的所有数字都已经存在，它们只需要按列表示.

I'm trying to do this through a stored proc. The assumption can be made that the number of columns (in terms of years) is fixed: 3.Also note, there are no arithmetic operations necessary - all the numbers I need are already there, they just need to be represented column-wise.

推荐答案

有几种方法可以将数据行转换为列.

There are several ways that you can convert the rows of data into columns.

在SQL Server中，您可以使用PIVOT函数:

In SQL Server you can use the PIVOT function:

select batsman, [2012], [2011], [2010]
from
(
  select batsman, runs, year
  from yourtable
) d
pivot
(
  sum(runs)
  for year in ([2012], [2011], [2010])
) piv;

或者您可以将聚集函数与CASE表达式一起使用:

Or you can use an aggregate function with a CASE expression:

select batsman,
  sum(case when year = 2012 then runs else 0 end) [2012],
  sum(case when year = 2011 then runs else 0 end) [2011],
  sum(case when year = 2010 then runs else 0 end) [2010]
from yourtable
group by batsman;

如果您具有已知的列数，则另一个版本将非常有用.但是，如果您要使用未知数量的year值，那么您将需要使用动态SQL:

The other version will work great if you have a known number of columns. But if you are going to have an unknown number of year values, then you will need to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT batsman,' + @cols + '
            from
            (
                select batsman, runs, year
                from yourtable
            ) x
            pivot
            (
                sum(runs)
                for year in (' + @cols + ')
            ) p '

execute(@query)

这篇关于将一列中的多行数据表示为一行SQL Server中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！