I have a table on my server where they have already pivoted the data so now I am sitting with a table that looks something like this.

this table gets updated monthly so to automate the script I want to be able to do something like this

Select Client_No
, Sum(column like '2022%')
From [table_name]

Is this possible? Basically I want the script to sum all the columns that start with 2022, and yes I am currently running this in SSMS

CodePudding user response：

To do a Sum, you usually need a GROUP BY clause. In this case you might be better-off using a sub-query to create a calculated column for grouping. Try this:

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

CodePudding user response：

Since you mentioned that you already have table which is pivoted you will have to unpivot it first.

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

Or if you don't want to put down all columns you can use dynamic unpivot

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  

Note: You can use STRING_AGG starting from SQL Server 2017 (14.x) and later