从Pivot对动态列执行操作 [英] Perform Operations on Dynamic Columns from Pivot

问题描述

如问题"所述，我正在尝试使用Pivot从动态sql查询向TempTable中插入一些值，因此我不知道ExistingCoulmnName

As The Question says that I'm Trying to insert some values from dynamic sql Query to TempTable using Pivot, so the ExistingCoulmnName are not known to me

下一步，我要执行一些算术运算，比如说对那些列值进行乘法.

In the next step I want to perform some arithmatic operation lets say multipliation on those Column Values.

我该怎么做?

附加一些示例:

select @cols = STUFF((SELECT ',' + QUOTENAME(FYYear) 
                from #TempCapEx
                group by FYYear
                order by FYYear
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'') 

set @query = 'SELECT CapitalExpenditureId,' + @cols + ' into  
         ##GlobalTempForCapExPivotResult from 
         (
            select CapitalExpenditureId, FYYear, indicatorvalue
            from #TempCapEx
        ) x
        pivot 
        (
            sum(indicatorvalue)
            for FYYear in (' + @cols + ')
        ) p'

execute (@query ) ;

所以我有一个##GlobalTempForCapExPivotResult:

CapitalExpenditureId    2016-2017   2017-2018   2018-2019   2019-2020   2020-2021   2021-2022   2022-2023
22150                   0.0000      35200.0000  35200.0000  35200.0000  35200.0000  35200.0000  NULL
32632                   NULL        213695.0000 224379.0000 235599.0000 247379.0000 259748.0000 0.0000
1589                    10252.0000  170370.0000 0.0000      0.0000      0.0000      0.0000      NULL
14988                   0.0000      133000.0000 0.0000      0.0000      0.0000      0.0000      NULL
36877                   NULL        303.0300    404.040     101.010     0.0000      0.0000      0.0000

因此，财政年度"列可能会增加或减少，所以我该怎么做:

So the Financial Year Columns may increase or decrease, so how can I do somthing like this :

Select [ExistingCoulmnName] * 3.5 from #GlobalTempForCapExPivotResult where [ExistingCoulmnName] = '[2016-2017]'

预期输出:

CapitalExpenditureId    2016-2017   2017-2018        2018-2019          2019-2020         2020-2021        2021-2022        2022-2023
22150                   0.00 * 3.5  35200.0000 * 3.5 35200.0000 * 3.5  35200.0000 * 3.5  35200.0000 * 3.5 35200.0000 * 3.5  NULL * 3.5

推荐答案

在标准SQL语句中没有本地方法可以做到这一点.相反，您使用某种语言(甚至可能是T-SQL)将新查询写入字符串，然后执行该新查询.与您的示例几乎相同.

There is no native way to do that inside a standard SQL statement. Instead you use some language (possibly even T-SQL) to write a new query in to a string, and then execute that new query. Much the same as your example already does.

DECLARE @field NVARCHAR(256) = N'2015-2016';

DECLARE @dynamicSelect NVARCHAR(MAX);

SET @dynamicSelect = N'SELECT *, 3.5 * [' + @field + N'] AS new_field FROM ##GlobalTempForCapExPivotResult';

EXECUTE(@dynamicSelect);

另外，请注意，这种类型的问题是 为什么 .您不应该首先创建该全局临时表.规范化的数据结构比SQL更友好.进行SQL的 外部 格式化.

Also, note that this type of problem is WHY you shouldn't make that global temporary table in the first place. Normalised data structures are much friendlier to SQL. Do the formatting outside of SQL.

这是没有全局临时表的查询...

This is your query without the global temporary table...

SELECT
  CapitalExpenditureId,
  FYYear,
  indicatorvalue * 3.5
FROM
  #TempCapEx
GROUP BY
  CapitalExpenditureId,
  FYYear
ORDER BY
  CapitalExpenditureId,
  FYYear

您 作为人类，您可能更喜欢平面文件(excel样式)表，但这是一个非常糟糕的主意在SQL内部使用的表的类型.坚持规范化的数据结构，并学习如何使用它们.不要试图破坏SQL.

You as a human being might prefer the flat file (excel style) table, but it is a really bad idea to make that type of table for use inside SQL. Stick to normalised data structures, and learn how to use them. Don't try to break SQL.

这篇关于从Pivot对动态列执行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！