Sommation de sous-sets avec SQL

J'ai deux tables comme celle-ci:

| SalesId | SalesDate | SalesStore | | StoreId | Name | |---------|-----------|------------| |---------|---------| | 1 | 5/3/17 | 3 | | 1 | Store A | | 2 | 2/2/18 | 3 | | 2 | Store B | | 3 | 6/6/17 | 2 | | 3 | Store C | | 4 | 7/8/17 | 3 | 

Je voudrais savoir s'il est possible de générer la sortie suivante en utilisant uniquement SQL:

 | Year | Store A | Store B | Store C | |---------|---------|---------|---------| | 2017 | 0 | 1 | 2 | | 2018 | 0 | 0 | 1 | 

Fondamentalement, je veux la sum des ventes de chaque magasin par année.

J'ai été en mesure d'get le total de tous les magasins en utilisant:

 SELECT YEAR(SalesDate) [Year], Count(1) [Sales Count] FROM Sales GROUP BY YEAR(SalesDate) ORDER BY 1 

Mais j'aimerais savoir s'il est possible de le faire en utilisant une seule requête SQL.

Voici une solution de tableau croisé dynamic capable de gérer n'importe quel nombre de magasins …

 IF OBJECT_ID('tempdb..#Store', 'U') IS NOT NULL DROP TABLE #Store; CREATE TABLE #Store ( StoreId INT NOT NULL PRIMARY KEY, StoreName CHAR(7) NOT NULL ); INSERT #Store (StoreId, StoreName) VALUES (1, 'Store A'), (2, 'Store B'), (3, 'Store C'); IF OBJECT_ID('tempdb..#Sales', 'U') IS NOT NULL DROP TABLE #Sales; CREATE TABLE #Sales ( SalesId INT NOT NULL PRIMARY KEY, SalesDate DATE NOT NULL, SalesStore INT NOT NULL ); INSERT #Sales (SalesId, SalesDate, SalesStore) VALUES (1, '2017-05-03', 3), (2, '2018-02-02', 3), (3, '2017-06-06', 2), (4, '2017-07-08', 3); --SELECT * FROM #Store st; --SELECT * FROM #Sales s; --========================================================= DECLARE @StoreCols VARCHAR(8000) = '', @sql VARCHAR(8000) = '', @DeBug BIT = 0; SELECT @StoreCols = CONCAT(@StoreCols, ', [', st.StoreName, '] = COUNT(CASE WHEN s.SalesStore = ', st.StoreId, ' THEN 1 END)') FROM #Store st ORDER BY st.StoreId; SET @sql = CONCAT(' SELECT [Year] = YEAR(s.SalesDate)', @StoreCols, ' FROM #Sales s GROUP BY YEAR(s.SalesDate);') IF @DeBug = 1 BEGIN PRINT(@sql); END ELSE BEGIN EXEC(@sql); END; 

Les resultats…

 Year Store A Store B Store C ----------- ----------- ----------- ----------- 2017 0 1 2 2018 0 0 1 

HTH, Jason

Je l'ai eu pour travailler en créant une table PIVOT pour chaque année, puis en utilisant une UNION pour les combiner.

 SELECT '2017' AS 'Year',[Store A],[Store B],[Store C] FROM ( SELECT YEAR(Sales.SalesDate) 'SalesYear',Store.[Name] FROM Sales JOIN Store ON Sales.SalesStore = StoreId WHERE YEAR(Sales.SalesDate) = 2017) AS table2017 PIVOT ( COUNT(SalesYear) FOR [Name] IN ([Store A],[Store B],[Store C]) ) AS pivotTable2017 UNION SELECT '2018' AS 'Year',[Store A],[Store B],[Store C] FROM ( SELECT YEAR(Sales.SalesDate) 'SalesYear',Store.[Name] FROM Sales JOIN Store ON Sales.SalesStore = StoreId WHERE YEAR(Sales.SalesDate) = 2018)AS table2018 PIVOT ( COUNT(SalesYear) FOR [Name] IN ([Store A],[Store B],[Store C]) ) AS pivotTable2018 

RÉSULTATS

 +------+----------+----------+---------+ | Year | Store A | Store B | Store C | +------+----------+----------+---------+ | 2017 | 0 | 1 | 2 | | 2018 | 0 | 0 | 1 | +------+----------+----------+---------+