如何从变量传递列别名 [英] How to pass columns alias names from variables

问题描述

 声明  @ year1   varchar （ 10 ）= '  1998' 
 声明  @ year2   varchar （ 10 ）= '  1999 ' 
 
 选择 
 sum（ case  < span class =code-keyword>当年= @ year1 然后 sales  end ） '  1998'   -    @ year1  
，sum（ case  何时 year = @ year2 然后 sales  end ）'  1999'   -    @ year2  
 来自 salestable 

在上面的查询中，我想将变量值作为列别名传递，而不是给出静态列名。 case。

如果我通过2004年的变量意味着希望将2004作为列名...

解决方案

将行转换为列，使用 PIVOT [ ^ ]，而不是 CASE WHEN ... END 声明。

  DECLARE   @cols   VARCHAR （ 300 ）
  DECLARE  < span class =code-sdkkeyword> @ dt   VARCHAR （ 2000 ）
  DECLARE   @ pt   VARCHAR （ 4000 ）
 
  SET   @cols  = '  [1998]，[1999]，[2000]' 
 
  SET   @ dt  = '  SELECT ProductID，YEAR（SalesDate）AS [Year]，Sales FROM ProductSales' 
 
  SET   @ pt  = '  SELECT ProductID，' + < span class =code-sdkkeyword> @cols  + ' ' + 
 '  FROM（' +  @ dt  + ' ）AS DT' + 
 '   PIVOT（SUM（Sales）FOR [Year] IN（' +  @cols  + < span class =code-string>' ））AS PT' 
  EXEC （ @ pt ）

如需了解更多信息，请参阅：

http：// blog .sqlauthority.com / 2008/06/07 / sql-server-pivot-and-unpivot-table-examples / [ ^ ]

在SQL查询中使用Pivot的简单方法 [ ^ ]

如何在T-SQL中执行数据透视操作..？[ ^ ]

试试



总和（ year = @ year1然后销售为@ year1 end）

 声明  @ year1   varchar （ 10 ）= '  199 8' 
 声明  @ year2   varchar （ 10 ）= '  1999 '， @ Sql   nvarchar （ 200 ）
 
 设置  @ Sql  = ' 选择总和（年份=' +  @ year1  + < span class =code-string>' 然后销售结束）[' +  @ year1  + ' ]，sum（年份=' +  @ year2  + ' 然后销售结束）[' +  @ year2  + ' ]来自salestable' 
 
   -    Sql Data将 
   -   选择总和（年份= 1998年然后销售结束时的情况）[1998]， 
   -    sum（年份= 1999年销售结束时的情况）[1999]  
   -   来自salestable  
 
  Exec （ @sql ）

Declare @year1 varchar(10)='1998'
Declare @year2 varchar(10)='1999'

select
     sum(case when year=@year1 then sales end) '1998'--@year1
    ,sum(case when year=@year2 then sales end) '1999'--@year2
from salestable

In the above query instead of giving static column names i wanna pass the variable value as a column alias name in case.
If i pass 2004 for variable means in want to get 2004 as a column name...

解决方案

To convert rows as columns, use PIVOT[^], instead doing it with CASE WHEN ... END statement.

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)

SET @cols = '[1998],[1999],[2000]'

SET @dt = 'SELECT ProductID, YEAR(SalesDate) AS [Year], Sales FROM ProductSales'

SET @pt = 'SELECT ProductID, ' + @cols + ' ' +
          'FROM (' + @dt + ') AS DT ' +
          'PIVOT(SUM(Sales) FOR [Year] IN(' + @cols + ')) AS PT'
EXEC(@pt)

For further information, please see:
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]
Simple Way To Use Pivot In SQL Query[^]
How to Perform Pivot Operation in T-SQL..?[^]

try

sum(case when year=@year1 then sales as @year1 end)

Declare @year1 varchar(10)='1998'
Declare @year2 varchar(10)='1999', @Sql nvarchar(200)

Set @Sql ='Select sum(case when year=' + @year1 +' then sales end) [' + @year1 +'],sum(case when year=' + @year2 + ' then sales end) [' + @year2 +'] from salestable'

-- Sql Data will be
-- Select sum(case when year=1998 then sales end) [1998],
--        sum(case when year=1999 then sales end) [1999] 
-- from salestable

Exec (@Sql)

这篇关于如何从变量传递列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！