TSQL pour compter les champs et faire un résumé

Question

Je n'ai aucune expérience de ce type de consolidation, mais je suppose que c'est la routine (je l'espère). C'est le fait de compter les colonnes qui me perturbe. Les données réelles sont de ~20k lignes :

Format des données :

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

Résultats nécessaires :

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

Et a roulé jusqu'à Owner

Owner   JobCount
Joe     5
Frank   3

Answer 1

Je pense que la suite PIVOT est la meilleure, puisque le nombre d'emplois pourrait augmenter :

;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]

Les lignes commentées sont la première requête que vous avez demandée. J'ai d'abord créé une table temporaire #state comme ceci :

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

Answer 2

Pour l'État/propriétaire

select  State, Owner, count(cs.Jobs) as JobCount
from yourtable 
cross apply (values (Job1),(Job2),(Job3),(Job4)) cs (Jobs)
Group By State, Owner

Roulée jusqu'au propriétaire

select Owner, count(cs.Jobs) as JobCount
from yourtable 
cross apply (values (Job1),(Job2),(Job3),(Job4)) cs (Jobs)
Group by Owner

Note : On considère que les vides dans les données échantillons sont les suivants NULL valeurs dans le tableau

Answer 3

Voici votre TSQL pour le résultat 1

SELECT 
    State
    ,Owner 
    ,Sum (
    (
        CASE 
            WHEN Job1 IS NULL THEN 0 
            ELSE 1 
        END)+
        (CASE 
            WHEN  Job2 IS NULL THEN 0 
            ELSE 1 
        END) + 
        (CASE 
            WHEN Job3 IS NULL THEN 0 
            ELSE 1 
        END)+
        (CASE 
            WHEN Job4 IS NULL THEN 0 
            ELSE 1 
        END))
FROM table
GROUP BY State, OWNER

Answer 4

Une autre option... juste pour le fun est ENSEMBLES DE REGROUPEMENT

Vous obtiendrez Propriétaire/État ET le Propriétaire Niveau en un seul coup

Select [Owner]
      ,[State]
      ,JobCount = sum(isnull(sign(Job1),0)+isnull(sign(Job2),0)+isnull(sign(Job3),0)+isnull(sign(Job4),0))
From  YourTable
Group By Grouping Sets ([State],[Owner]),([Owner])
Order By case when [State] is null then 1 else 0 end

Renvoie à

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