Tableau 1
| MODULE | COUNT | YEAR | ------------------------- | M1 | 12 | 2011 | | M1 | 43 | 2012 | | M2 | 5 | 2011 | | M3 | 24 | 2011 | | M4 | 22 | 2011 | | M4 | 11 | 2012 | | M5 | 10 | 2012 |
Je veux afficher comme ça
| MODULE | 2011 | 2012 | ---------------------------- | M1 | 12 | 43 | | M2 | 5 | - | | M3 | 24 | - | | M4 | 22 | 11 | | M5 | - | 10 |
Cela peut être fait en utilisant la requête PIVOT . Ou ce qui suit:
select Module, SUM(CASE WHEN Year='2011' then Count ELSE 0 END) as [2011], SUM(CASE WHEN Year='2012' then Count ELSE 0 END) as [2012] FROM T GROUP BY Module
Démo SQL Fiddle
Vous pouvez utiliser PIVOT pour cela:
SELECT Module, [2011], [2012] FROM ( SELECT * FROM Table1 ) AS SourceTable PIVOT ( SUM([Count]) FOR [Year] IN ([2011], [2012]) ) AS PivotTable;
Vous pouvez également utiliser cette requête dynamic si vous n'avez pas d' Year
limitée
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME([Year]) from Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') SET @query = 'SELECT Module,' + @cols + ' FROM ( Select * FROM Table1 ) dta PIVOT ( SUM([Count]) FOR [Year] IN (' + @cols + ') ) pvt ' EXECUTE(@query);
Résultat:
| MODULE | 2011 | 2012 | ---------------------------- | M1 | 12 | 43 | | M2 | 5 | (null) | | M3 | 24 | (null) | | M4 | 22 | 11 | | M5 | (null) | 10 |
Vous pouvez également utiliser cette méthode dynamic alternative: (Dynamique de la requête donnée par @valex )
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SELECT @cols = STUFF((SELECT distinct ',' + ' SUM(CASE WHEN YEAR= ''' + CAST(Year AS varchar(50)) + ''' THEN [COUNT] ELSE ''-'' END) AS ' + QUOTENAME([Year]) from Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') SET @query = 'SELECT Module, ' + @cols + ' FROM Table1 GROUP BY Module' EXECUTE(@query);
SELECT * FROM Tablename PIVOT ( AVG([Count] FOR [Year] IN (2011, 2012, 2013)) AS AvgCount )
Je pense que vous devez regarder dans l'opérateur relationnel PIVOT.
Utilisation de PIVOT et UNPIVOT
SELECT Module, [2011], [2012] FROM T PIVOT (SUM(Count) FOR Year IN ([2011], [2012])) AS PivotTable;