Comment puis-je faire défiler les valeurs d'une table dans une déclaration de cas?

(Désolé, nouveau à SQL). J'ai le tableau suivant que les users peuvent mettre à jour et / ou append à:

Project Type: ID Name 1 Documents 2 DVD 3 Poster 

J'ai une requête qui en partie, doit faire une boucle à travers chaque logging et créer un (pivot?):

 ... sum (case when Project.Name = Documents then 1 else 0 end) as NumOf + Documents sum (case when Project.nextvalue = nextvalue then 1 else 0 end) as NumOf + nextvalue ... 

… puis faites une boucle pour chaque logging dans le tableau.

donc les résultats ressembleront à:

 ProductName NumOfDocuments NumOfDVD NumOfPoster Product A 6 0 4 Product B 13 3 8 Product C 2 0 1 

Est-ce possible?

MODIFIER

Voici ma requête en cours:

 SELECT Clients.ID, Products.Name, sum(case when ProjectTypes.Name = 'Abstracts' then 1 else 0 end) as NumAbstracts FROM Clients INNER JOIN Products ON Clients.ID = Products.ClientID INNER JOIN Projects ON Products.ID = Projects.ProductID INNER JOIN ProjectTypes ON Projects.ProjectTypeID = ProjectTypes.ID GROUP BY Clients.ID, Products.Name, Projects.ProjectTypeID /* SCHEMA Clients: ID | Name Products: ID | Name | Client ID Projects: ID | Name | ProductID | ProjectTypeID ProjectTypes: ID | Name */ 

    Vous pouvez accomplir ceci avec l'agrégation conditionnelle, comme vous l'avez indiqué.

     SELECT ProductName, SUM(CASE WHEN Name = 'Documents' THEN 1 ELSE 0 END) as NumOfDocuments, SUM(CASE WHEN Name = 'DVD' THEN 1 ELSE 0 END) as NumOfDVD, SUM(CASE WHEN Name = 'Poster' THEN 1 ELSE 0 END) as NumOfPoster FROM YourTable GROUP BY ProductName 

    DYNAMIC SQL

     IF OBJECT_ID('tempdb..#Clients') IS NOT NULL DROP TABLE #Clients IF OBJECT_ID('tempdb..#Products') IS NOT NULL DROP TABLE #Products IF OBJECT_ID('tempdb..#Projects') IS NOT NULL DROP TABLE #Projects IF OBJECT_ID('tempdb..#ProjectTypes') IS NOT NULL DROP TABLE #ProjectTypes CREATE TABLE #Clients (ID int, Name varchar(64)) CREATE TABLE #Products (ID int, Name varchar(64), ClientID int) CREATE TABLE #Projects (ID int, Name varchar(64), ProductID int, ProjectTypeID int) CREATE TABLE #ProjectTypes (ID int, Name varchar(64)) INSERT INTO #Clients (ID, Name) VALUES (1,'Client1'), (2,'Client2'), (3,'Client3'), (4,'Client4') INSERT INTO #Products (ID, Name, ClientID) VALUES (1,'Prod1',1), (2,'Prod2',1), (3,'Prod3',1), (2,'Prod2',2), (2,'Prod2',3), (3,'Prod3',3), (4,'Prod3',3) INSERT INTO #Projects (ID, Name, ProductID, ProjectTypeID) VALUES (1,'Proj1',1,1), (1,'Proj1',2,1), (1,'Proj1',3,1), (2,'Proj2',2,2), (2,'Proj2',3,2), (2,'Proj2',4,2), (3,'Proj3',4,3) INSERT INTO #ProjectTypes (ID, Name) VALUES (1,'ProjType1'), (2,'ProjType2'), (3,'ProjType3') IF OBJECT_ID('tempdb..#Summary') IS NOT NULL DROP TABLE #Summary SELECT c.ID, prod.Name as ProductName, proj.Name as ProjectName, projT.Name as ProjType INTO #Summary FROM #Clients c INNER JOIN #Products prod on prod.ClientID = c.ID INNER JOIN #Projects proj on proj.ProductID = prod.ID INNER JOIN #ProjectTypes projT on projT.ID = proj.ProjectTypeID SELECT * FROM #Summary DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(ProjType) FROM (SELECT DISTINCT ProjType FROM #Summary) AS Names --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT ProductName, ' + @ColumnName + ' FROM #Summary PIVOT(COUNT(ProjType) FOR ProjType IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery 

    Donc, en utilisant vos tables, vous devriez pouvoir exécuter ceci … il peut avoir besoin d'un léger ajustement.

     IF OBJECT_ID('tempdb..#Summary') IS NOT NULL DROP TABLE #Summary SELECT c.ID, prod.Name as ProductName, proj.Name as ProjectName, projT.Name as ProjType INTO #Summary FROM Clients c INNER JOIN Products prod on prod.ClientID = c.ID INNER JOIN Projects proj on proj.ProductID = prod.ID INNER JOIN ProjectTypes projT on projT.ID = proj.ProjectTypeID DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(ProjType) FROM (SELECT DISTINCT ProjType FROM #Summary) AS Names --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT ProductName, ' + @ColumnName + ' FROM #Summary PIVOT(COUNT(ProjType) FOR ProjType IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery 

    Didacticiel