Number of numeric columns per row with tSQL

Question

I am performing a task for my work, and, unfortunately, the tables are created terribly, and I cannot do too much to change the structure (one of our main programs here has been based on outdated tables for many years). In doing so, I need to find a way to use SQL Server (TSQL) to count the number of different columns in a given row.

Example:

I have a table with columns name , fieldA , fieldB , fieldC , fieldD , etc. I want to return a table with each row, returning the name and the number of different columns for fieldA - fieldD

Visually:

• Jim - 1 - 3 - 4 - 6
• John - 1 - 1 - 1 - 2
• Jane 2 - 2 - 3 - 3

Will return

• Jim - 4
• John - 2
• Jane - 2

Answer 1

One way to do this is to reveal the data and then group it again. This gives you the option to use count(distinct) :

 select name, count(distinct val) from t unpivot (val for col in (FieldA, FieldB, FieldC, FieldD)) unpvt group by name; 

However, the most efficient way to do this is to keep all processing in one line - no join or group by or union s. Assuming none of the values ​​is NULL , the following holds for the four columns:

 select name, 4 - ((case when FieldA in (FieldB, FieldC, FieldD) then 1 else 0 end) + (case when FieldB in (FieldC, FieldD) then 1 else 0 end) + (case when FieldC in (FieldD) then 1 else 0 end) ) from t; 

That is, start with the total number of columns. Then subtract 1 each time a column looks like a column that comes later. This last condition ensures that duplicates are not counted more than once.

Answer 2

 DECLARE @x TABLE ( Name VARCHAR(32), A VARCHAR(32), B VARCHAR(32), C VARCHAR(32), D VARCHAR(32) ); INSERT @x VALUES ('Jim', 1,3,4,6), ('John',1,1,1,2), ('Jane',2,2,3,3); SELECT Name, NumCols = COUNT(DISTINCT x) FROM @x AS x UNPIVOT (x FOR y IN (A,B,C,D)) AS up GROUP BY Name ORDER BY NumCols DESC; 

Answer 3

You can use the UNPIVOT operator to translate columns into rows and then count different values:

 select c_name , count(distinct val) as distinct_vals , count(val) as total_vals from ( select c_name , val from t1 unpivot( val for col in(col1, col2, col3, col4) ) unpvt ) s group by c_name 

result:

 C_NAME DISTINCT_VALS TOTAL_VALS Jane 2 4 Jim 4 4 John 2 4 

SQLFIDDLE Demo

Answer 4

Another way:

 SELECT Name, Cnt = (SELECT COUNT(DISTINCT V) FROM (VALUES (A), (B), (C), (D)) AS v (V)) FROM atable ; 

Uses SQL Server 2008 syntax ( VALUES string constructor). It can be adapted for earlier versions by replacing VALUES (A), (B), ... with SELECT A UNION ALL SELECT B UNION ALL ...

Answer 5

 Select Name,Count(*) From (select Name,FieldA from test10 Union select Name,FieldB from test10 union select Name,FieldC from test10 Union select Name,FieldD from test10)as a Group by name