如何在年份和年份-1的列中编写查询值？

I have a SQL Server 2008 database with this table:

我有一个SQL Server 2008数据库与此表：

id   | Data       | OtherData  | Year |
-----+--------+----------------+------- 
001AA  | 156540     | 195896     | 2014 
405BB  | 111342.54  | 79263      | 2014 
001AA  | 42550      | 216417     | 2015 
056CC  | 26900      | 352194     | 2014 
023DD  | 23524      | 139124     | 2014 
023DD  | 21659.80   | 108143     | 2015 
405BB  | 111212.65  | 111449     | 2015 
056CC  | 64871.95   | 541478     | 2015

I would like this:

我想这个：

id   | Data 2014  | Data 2015   |
-----+------------+-------------+- 
001AA  | 156540     | 42550       | 
405BB  | 111342.54  | 111212.65   |   
056CC  | 26900      | 64871.95    | 
023DD  | 23524      | 21659.80    |

Any suggestions? Thanks

有什么建议么？谢谢

4 个解决方案

#1

You have yo pivot data!

你有转轴数据！

SELECT id, [2014], [2015]
FROM YourTable AS Dt
PIVOT (MAX(Data) FOR Year IN ([2014], [2015])) AS PVT

See: Using PIVOT and UNPIVOT

请参阅：使用PIVOT和UNPIVOT

#2

You can use CASE WHEN with MAX function.

你可以使用带有MAX功能的CASE WHEN。

SELECT id,
      MAX(case when [Year]= '2014' then Data end) as 'Data 2014',
      MAX(case when [Year]= '2015' then Data end) as 'Data 2015'     
FROM T
GROUP BY id

sqlfiddle

#3

Using pivot

使用枢轴

Select id, max([2014]) as [Data 2014], max([2015]) as [Data 2015] from tbl as tm 
pivot (max(data) for [year] in ([2014],[2015])) as pvt 
group by id

EDIT:

编辑：

You'll need dynamic query for that.

你需要动态查询。

declare @myYear as smallint;
declare @myYearN_1 as smallint;
set @myYear = 2015;
set @myYearN_1 = 2014;

Set @query = 'Select id, max(' + @myYear +') as [Data_N], max(' + @myYearN_1 + ') as [Data N-1] from tbl as tm 
pivot (max(data) for [year] in (' + @myYear,@myYearN_1 + ')) as pvt 
group by id'

execute(@query)

#4

I would like to use pivot with parameters but it faults: Error Message: Failed to convert from nvarchar to smallint...Incorrect value "@myYear" specified in the PIVOT operator.

我想使用带参数的数据但它有故障：错误消息：无法从nvarchar转换为smallint ...在PIVOT运算符中指定了错误的值“@myYear”。

    declare @myYear as smallint;
    declare @myYearN_1 as smallint;
    set @myYear = 2015;
    set @myYearN_1 = 2014;
    Select id, max(@myYear) as [Data N], max(@myYearN_1) as [Data N-1] from [tbl] as tm 
    pivot (max(data) for [year] in ([@myYear],[@myYearN_1])) as pvt 
    group by id

#1