如何在case语句中遍历表中的值 [英] How can I loop through values from a table in a case statement

问题描述

(对不起，SQL新手). 我有下表，用户可以更新和/或添加到表中:

(Sorry, new to SQL). I have the following table which users can update and/or add to:

Project Type:
ID   Name  
1    Documents
2    DVD
3    Poster

我有一个查询，部分需要遍历每条记录并创建一个(枢轴?):

I have a query that in part, needs to loop through each record and create a (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
...

...然后遍历表中的每个记录.

...and then loop through for each record in table.

因此结果将如下所示:

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

这可能吗?

编辑

这是我当前的查询:

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
*/

推荐答案

您可以使用条件聚合来完成此操作，就像您列出的那样.

You can accomplish this with conditional aggregation, like you had listed.

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

动态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

因此，使用您的表，您应该能够只运行它...可能需要稍作调整.

So using your tables, you should be able to just run this... it may need slight tweaking.

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

教程

这篇关于如何在case语句中遍历表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！