sql - TSQL统计字段并汇总

Question

没有这种整合的经验，但我希望它是例行公事(希望如此)它计算让我感到困惑的列。实际数据约为 20k 行:

数据格式:

State   Owner   Job1    Job2    Job3    Job4
TN      Joe     123     456             234 
TN      Frank           456     789     
FL      Joe     123     456
FL      Frank   123

需要的结果:

State Owner JobCount
TN  Joe     3
TN  Frank   2
FL  Joe     2
FL  Frank   1

并汇总到 Owner

Owner   JobCount
Joe     5
Frank   3

Answer 1

我猜 PIVOT 套件最好，因为工作数量可能会增加:

;WITH cte AS 
(SELECT [State]
      ,[Owner]
      ,[Job]
      ,[JobN]
FROM (
        SELECT
             [State]
            ,[Owner]
            ,Job1
            ,Job2
            ,Job3
            ,Job4
        FROM #state
     ) AS p
UNPIVOT
(JobN FOR [Job] IN
(Job1,Job2,Job3,Job4)
) AS unpvt)
--SELECT [State], [Owner], COUNT(1) AS JobCount
--FROM cte
--GROUP BY [State], [Owner]
SELECT [Owner], COUNT(1) AS JobCOunt
FROM cte
GROUP BY [Owner]

注释行是您请求的第一个查询。我主要创建了一个临时表#state，如下所示:

CREATE TABLE #state
(
    [State] VARCHAR(2)
    ,[Owner] VARCHAR(20)
    ,[Job1] INT
    ,[Job2] INT
    ,[Job3] INT
    ,[Job4] INT
)