SQL Server 2008 R2:将别名与列值连接起来。

I have Product table with columns QtyID, Qty and Year_ID. I also have a table tbl_Years with ID and Year.

我有包含QtyID、Qty和Year_ID列的产品表。我还有一个带ID和年的tbl_Years表。

I have a simple SELECT statement with SUM calculation of Qty:

我有一个简单的选择语句，计算Qty的和:

SELECT 
    SUM(Qty) AS /*Sum_of_year_2016*/
FROM 
    Product p
INNER JOIN 
    tbl_Years ty ON p.Year_ID = ty.Year_ID
WHERE
    ty.Year_ID = 6;

I want to define an alias name of Sum_of_year_2016 for the SUM(Qty) value.

我想为SUM(Qty)值定义Sum_of_year_2016的别名。

Note: the year should be fetched from the tbl_Years table.

注意:应该从tbl_Years表获取年份。

My attempt:

我的尝试:

SELECT 
    SUM(Qty) AS 'Sum_of_year_' + ty.Year
FROM 
    Product p
INNER JOIN  
    tbl_Years ty ON p.Year_ID = ty.Year_ID
WHERE 
    ty.Year_ID = 6;

But I'm getting an error:

但我犯了一个错误:

Syntax error; incorrect syntax near '+'.

语法错误;不正确的语法“+”附近。

1 个解决方案

#1

You need to use dynamic SQL to get custom alias:

您需要使用动态SQL获取自定义别名:

DECLARE @year NVARCHAR(4) = (SELECT TOP 1 Year FROM  tbl_Years WHERE Year_id=6);

DECLARE @sql NVARCHAR(MAX) = 
'SELECT 
    SUM(Qty) AS ' + QUOTENAME('Sum_of_year_' + @year) +
' FROM Product p
INNER JOIN tbl_Years ty 
ON p.Year_ID = ty.Year_ID
Where ty.Year_ID = 6;';

EXEC sp_executesql @sql;