TSQL to count fields and summarize

Question

No experiences with this kind of consolidation, but I expect it's routine (hope so) Its counting the columns that throws me. Actual data is ~20k rows:

Data format:

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

Results needed:

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

And rolled up to Owner

Owner   JobCount
Joe     5
Frank   3

Answer 1

I guess PIVOT suites best, since jobs number might increase:

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

Commented rows are the first query you requested. I've primarily created a temp table #state like this:

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

Answer 2

Here is your TSQL for result 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