CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2008-r2
DtotheG
DtotheG

Reputation: 1257

COUNT the number of columns where a condition is true? SQL Server 2008 R2

I have a table that looks something like

ID Col1 Col2 Col3 Col4
1  3     5    3    3

What I want to do is COUNT the number of 3s in this particular row.

I have tried the 

select COUNT(*) 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'TableName'   -- but obviously I need WHERE Col1 = 3 OR Col2 = 3...

What would be the best way to achieve this?

Upvotes: 1

Views: 2205

Answers (3)

Karthik D V
Karthik D V

Reputation: 926

Please find the sample code:

            DECLARE @Query VARCHAR(MAX) = 'SELECT Count = '
        SELECT 
        @Query += '( CASE WHEN '+ COLUMN_NAME + ' = 3 THEN 1 ELSE 0 END ) + ' 
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST' AND COLUMN_NAME <> 'ID'
        SET @Query = SUBSTRING(@Query, 1, DATALENGTH(@Query) - 2) + ' FROM TEST WHERE ID = 1'
        EXEC(@Query)

Upvotes: 0

Serge
Serge

Reputation: 6712

I don't really enjoy working with PIVOT so here a solution using APPLY.

SELECT
    T.id
    , Val
    , COUNT(*)
FROM MyTable AS T
CROSS APPLY (
    VALUES
        (T.C1)
        , (T.C2)
        , (T.C3)
        , (T.C4)
) AS X(Val)
GROUP BY T.Id, X.Val
ORDER BY T.Id, X.val

Upvotes: 2

hashbrown
hashbrown

Reputation: 3516

Based on what OP asked, this can be done

select 
CASE WHEN Col1 = 3 then 1 ELSE 0 END +
CASE WHEN Col2 = 3 then 1 ELSE 0 END +
CASE WHEN Col3 = 3 then 1 ELSE 0 END +
CASE WHEN Col4 = 3 then 1 ELSE 0 END
From TableName

Upvotes: 2

Related Questions