Comment additionner plusieurs champs dans un seul champ dans une requête SQL ?

Question

J'ai beaucoup de code dans l'entrée, ce code a un parent

SELECT [fromAccCode]= 
CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611,201612,209050,
209060,209090,209110,213040,213163,213165,213166,213190) THEN r1
WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150
,201155) THEN r2 
WHEN [fromAccCode] IN (201443,206020,207010,207040,207100,207130,209612) THEN r3

et j'ai configuré l'entrée avec ce code

SELECT [fromAccCode] FROM [dbo].[source_table] WHERE ','+'201010, 
201020,259861,754265,213040,213163,213165,201040,
201048, 201050,201443,206020'+',' like '%,'+cast([fromAccCode] 
AS varchar(10))+',%'

Je veux que vous ayez 3 enregistrements 1,2,3 de sum des valeurs(r1), sum of values(r2) et sum of values(r3) and this fields have values

Answer 1

Initialement, j'ai supposé que vous pourriez diviser les QUAND puis les SOMMER.

SELECT 
SUM(CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190) THEN r1 END) AS TotalR1,
SUM(CASE WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150, 201155) THEN r2 END) AS TotalR2,
SUM(CASE WHEN [fromAccCode] IN (201443, 206020, 207010, 207040, 207100, 207130, 209612) THEN r3 END) AS TotalR3
FROM [dbo].[source_table]
WHERE [fromAccCode] IN (
  201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190,
  201040, 201048, 201050, 201090, 201120, 201150, 201155,
  201443, 206020, 207010, 207040, 207100, 207130, 209612);

Mais il semble que vous recherchiez quelque chose comme cette monstruosité.

SELECT src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END) AS rx,
SUM([value]) as SumValues
FROM [dbo].[source_table] as src
JOIN (SELECT DISTINCT fromAccCode, TypeR FROM (VALUES 
 (201010,1), (201020,1), (201442,1), (201521,1), (201611,1), (201612,1), (209050,1), (209060,1), (209090,1), (209110,1), 
 (213040,1), (213163,1), (213165,1), (213166,1), (213190,1),
 (201040,2), (201048,2), (201050,2), (201090,2), (201120,2), (201150,2), (201155,2),
 (201443,3), (206020,3), (207010,3), (207040,3), (207100,3), (207130,3), (209612,3)
) val (fromAccCode, TypeR)) AS ref ON ref.fromAccCode = src.fromAccCode
GROUP BY src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END);

Au fait, à propos de ce critère LIKE.
En utilisant un CONCAT au lieu d'une conversion en varchar fonctionne également.

WHERE ',' + '201010,201020,259861,754265,213040,213163,213165,201040,201048,201050,201443,206020' + ',' 
      LIKE CONCAT('%,', [fromAccCode], ',%')

Answer 2

Premièrement : ajoutez un champ pour définir le nombre de cas

  select  [fromAccCode], --votre code
field_name=  --ajoutez ce code
          CASE 
      WHEN [fromAccCode] IN (201010,201020,201442,201521,201611,201612,209050,209060,209090,209110,213040,213163,213165,213166,213190) THEN 'r1'         
      WHEN [fromAccCode] IN (201040,201048,201050,201090,201120,201150,201155) THEN 'r2'
      WHEN [fromAccCode] IN (201443,206020,207010,207040,207100,207130,209612) THEN 'r3'     
               ELSE null
          END   

Vous n'avez pas besoin de ce extrait de code :

WHERE ',' + '201010,201020,259861,754265,213040,213163,213165,201040,201048,201050,201443,206020' + ',' 
  LIKE CONCAT('%,', [fromAccCode], ',%')

Parce que vous avez la valeur de fromAccCode

deuxièmement :

 select [fromAccCode ]
  ,[field_name]
  ,sum([value])
   from #result_table  group by [fromAccCode ]
  ,[field_name]
  ,[value]