Fusionner les colonnes dupliquées SQL à l'aide du groupe par

J'ai une table avec des valeurs comme celle-ci;

ACC SALES LY SALES TY YEAR ------------------------------------- B0022 0 15 2017 B0022 22 0 2016 AA000 0 12 2017 AA000 6 0 2016

Je cherche à créer une vue qui va merge des lignes avec le même ACC. Donc ce que je cherche est ceci;

 ACC SALES LY SALES TY YEAR -------------------------------------- B0022 22 15 EMPTY AA000 6 12 EMPTY

J'ai regardé les clauses GROUP BY , over(Partition by) et je ne peux tout simplement pas le faire fonctionner. Cela montre seulement une partie de ma table / vue actuelle.

Le code de la vue complète est ici:

 SELECT DISTINCT TOP (100) PERCENT dbo.[Sales By SKU Units].[Customer Account Number], CASE WHEN [Sales By SKU Units].[Customer Account Number] = 'S0040' THEN 'H Samuel' WHEN [Sales By SKU Units].[Customer Account Number] = 'Z0004' THEN 'Ernest Jones' WHEN [Sales By SKU Units].[Customer Account Number] = 'N0014' THEN 'Goldsmiths' WHEN [Sales By SKU Units].[Customer Account Number] = 'B0022' THEN 'Beaverbrooks' WHEN [Sales By SKU Units].[Customer Account Number] = 'A0097' THEN 'Fraser Hart' WHEN [Sales By SKU Units].[Customer Account Number] = 'H0085' THEN 'F Hinds' WHEN [Sales By SKU Units].[Customer Account Number] = 'A0044' THEN 'Amazon' WHEN [Sales By SKU Units].[Customer Account Number] = 'S0482' THEN 'Watch Shop' END AS CustomerName, dbo.[Sales By SKU Units].SKU, dbo.RangeLists.Column1 AS Status, CASE WHEN [Sales By SKU Value].[W/H Stock] IS NULL THEN '0' WHEN [Sales By SKU Value].[W/H Stock] = '' THEN '0' WHEN [Sales By SKU Value].[W/H Stock] IS NOT NULL THEN [Sales By SKU Value].[W/H Stock] END AS [Warehouse Stock], CASE WHEN [Sales By SKU Value].[Store Stock] IS NULL THEN '0' WHEN [Sales By SKU Value].[Store Stock] = '' THEN '0' WHEN [Sales By SKU Value].[Store Stock] IS NOT NULL THEN [Sales By SKU Value].[Store Stock] END AS [Store Stock], CASE WHEN [Sales By SKU Value].[On Order] IS NULL THEN '0' WHEN [Sales By SKU Value].[On Order] = '' THEN '0' WHEN [Sales By SKU Value].[On Order] IS NOT NULL THEN [Sales By SKU Value].[On Order] END AS [On Order], CASE WHEN [Sales By SKU Value].[Total Stock] IS NULL THEN '0' WHEN [Sales By SKU Value].[Total Stock] = '' THEN '0' WHEN [Sales By SKU Value].[Total Stock] IS NOT NULL THEN [Sales By SKU Value].[Total Stock] END AS [Total Stock], CASE WHEN [Sales By SKU Value].[No Of Stores] IS NULL THEN '0' WHEN [Sales By SKU Value].[No Of Stores] = '' THEN '0' WHEN [Sales By SKU Value].[No Of Stores] IS NOT NULL THEN [Sales By SKU Value].[No Of Stores] END AS [Number of Stores], CASE WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jan' THEN [Sales By SKU Units].[Jan] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'feb' THEN [Sales By SKU Units].[Feb] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'mar' THEN [Sales By SKU Units].[March] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'apr' THEN [Sales By SKU Units].[April] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'may' THEN [Sales By SKU Units].[May] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jun' THEN [Sales By SKU Units].[June] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jul' THEN [Sales By SKU Units].[July] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'aug' THEN [Sales By SKU Units].[August] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'sep' THEN [Sales By SKU Units].[September] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'oct' THEN [Sales By SKU Units].[October] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'nov' THEN [Sales By SKU Units].[November] WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'dec' THEN [Sales By SKU Units].[December] ELSE '0' END AS [Sales Last Month], dbo.RangeLists.pnum AS Model, CASE WHEN [Sales By SKU Units].[Year] = '2016' THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) ELSE '0' END AS [SALES LY], CASE WHEN [Sales By SKU Units].[Year] = '2017' THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) ELSE '0' END AS [SALES TY], dbo.[Sales By SKU Units].Year FROM dbo.[Sales By SKU Units] INNER JOIN dbo.[Sales By SKU Value] ON dbo.[Sales By SKU Units].Year = dbo.[Sales By SKU Value].Year AND dbo.[Sales By SKU Units].SKU = dbo.[Sales By SKU Value].SKU AND dbo.[Sales By SKU Units].[Customer Account Number] = dbo.[Sales By SKU Value].[Customer Account Number] INNER JOIN dbo.RangeLists ON dbo.[Sales By SKU Value].SKU = dbo.RangeLists.CustProdRef

Je sais que j'ai besoin de GROUP BY ou de OVER (partition par), mais je ne peux pas find comment les appliquer à ma requête actuelle.

Je suppose que vous searchz la sum des champs SALES , auquel cas vous avez besoin de SUM() pour votre regroupement, comme ceci:

 select ACC , sum(SALES_LY) as SALES_LY , sum(SALES_TY) as SALES_TY , null as [YEAR] from ([insert your current query here]) group by ACC

Si vous voulez le maximum, utilisez simplement MAX() au lieu de SUM()