在这种情况下如何使用数据透视表进行计算

本文关键字：计算 透视 数据 这种情况下 何使用 | 更新日期: 2023-09-27 18:30:25

如何从给定的数据中获取以下结果。

报告

-------------------------------------------------
 ID    |    Name          |    Status
-------------------------------------------------
  1    |     A            |    Inprogress
  2    |     A            |    Inprogress
  3    |     A            |    Complete

我需要像这样计算结果

---------------------------------------------
Name    |   Total  | Complete  |  Remaining
---------------------------------------------
   A    |    3     |   1       |    2

.SQL：

Select 
    [Inprogress], [Complete], [Printed] 
from  
    (select 
         specimenID, count([RStatus]) 
     from tbl_LabReport 
     group by specimenID) as sourcetable   
Pivot 
    (Count(specimenID) for [RStatus] in ( [Inprogress],[Complete],[Printed] ))  as ptv

在这种情况下如何使用数据透视表进行计算

您不需要使用PIVOT运算符。尝试使用subquery或CROSS APPLY运算符来解决您的任务

;WITH test_data AS(
    SELECT 1 AS id, 'A' AS NAME, 'Inprogress' AS [status]
    UNION ALL
    SELECT 2, 'A', 'Inprogress'
    UNION ALL
    SELECT 3, 'A', 'Complete'
    UNION ALL
    SELECT 4, 'B', 'Complete'
)
SELECT DISTINCT
       name,
       T3.*
FROM test_data AS T
CROSS APPLY (SELECT COUNT(*) AS total,
                    SUM(CASE WHEN T2.status = 'Complete' THEN 1 ELSE 0 END) AS complete, 
                    SUM(CASE WHEN T2.status = 'Inprogress' THEN 1 ELSE 0 END) AS remaining 
             FROM test_data AS T2
             WHERE T2.name = T.name) AS T3

你可以采取几种方法。 此示例使用 CASE 表达式。 如果您不熟悉这个概念，CASE 允许您有条件地返回选定的值。 我已将您的示例数据转换为表变量：

示例数据 

/* Creating sample data inside a table variable makes it
 * easy to share.
 */
DECLARE @Sample TABLE
    (
        ID            INT,
        Name        VARCHAR(50),
        [Status]    VARCHAR(50)
    )
;
/* Sample values taken from OP.
 */
INSERT INTO    @Sample
    (
        ID,
        Name,
        [Status]
    )
VALUES
    (1, 'A', 'Inprogress'),
    (2, 'A', 'Inprogress'),
    (3, 'A', 'Complete')
;

CASE 返回 1 表示所需状态，返回 0 表示其他所有状态。 对结果求和提供小计。

/* Using CASE to return conditional sub totals.
 */
SELECT
    Name,
    COUNT([Status])                                                AS Total,
    SUM(CASE WHEN [Status] = 'Complete'    THEN 1 ELSE 0 END)        AS Complete,
    SUM(CASE WHEN [Status] = 'Inprogress'    THEN 1 ELSE 0 END)    AS Remaining
FROM
    @Sample
GROUP BY
    Name
;
相关文章：