如果列名像'2020%'，是否可以对列求和

Question

我的服务器上有一个表，他们已经在那里透视了数据，所以现在我坐在一个看起来像这样的表上。

2022501><1>>

Client_No	20200201	20200401	20220101	20220401
12346789	3	1	0	0>
321654987	4	0	2	0

由于您提到您已经有了表，因此您必须首先取消透视它。

SELECT CLIENT_NO, SUM(CAST(ORDERS AS BIGINT)) OrdersNumber FROM (
SELECT Client_No, Years, Orders  
FROM   
(SELECT Client_No,[20200201], [20220101], [20200401], [20220201]
FROM myTable) p  
UNPIVOT  
(Orders FOR Years IN   
([20200201], [20200401], [20220101], [20220201])  
)AS unpvt
) SRC
WHERE LEFT(SRC.Years,4) = '2022'
GROUP BY CLIENT_NO

或者，如果你不想放下所有的列，你可以使用动态反透视

DECLARE @Pivot_Column [nvarchar](max);  
DECLARE @Query [nvarchar](max);  

set @Pivot_Column = (SELECT STRING_AGG('[' + cName + ']', ',') FROM  
(select c.Name cName from sys.all_columns c
left join sys.objects o on c.object_id = o.object_id 
where o.name = 'MyTable' and c.name <> 'Client_No' )Tab  
)

SELECT @Query='
SELECT CLIENT_NO, SUM(CAST(ORDERS AS BIGINT)) OrdersNumber FROM (
SELECT Client_No, Years, Orders  
FROM   
(SELECT Client_No,' + @Pivot_Column +  '
FROM myTable) p  
UNPIVOT  
(Orders FOR Years IN   
(' + @Pivot_Column +  ')  
)AS unpvt
) SRC
WHERE LEFT(SRC.Years,4) = ''2022''
GROUP BY CLIENT_NO
'   
EXEC  sp_executesql  @Query  

注意：您可以从SQL Server 2017(14.x(和更高版本的开始使用STRING_AGG

要执行Sum，通常需要GROUP BY子句。在这种情况下，您最好使用子查询来创建用于分组的计算列。试试这个：

SELECT Client_No, Yr, Sum(ItemValue)
FROM (
Select Client_No, 
Left(datemask_or_whatever, 4) as Yr, --column like '2022%'
ItemValue
From [table_name]
) SubQuery
GROUP BY Client_No, Yr