sql - Count Number of Distinct Columns in a Row with tSQL -

i doing task work , unfortunately tables designed horribly , can't far modifying structure (one of our major programs here has been based off these outdated tables years). being said, need find way use sql server (tsql) count number of distinct columns in given row.

example:

i have table columns name, fielda, fieldb, fieldc, fieldd, etc want return table each of rows, returning name , number of distinct columns fielda - fieldd

visually:

• jim - 1 - 3 - 4 - 6
• john - 1 - 1 - 1 - 2
• jane 2 - 2 - 3 - 3

would return

• jim - 4
• john - 2
• jane - 2

one way unpivoting data , subsequently grouping again. gives ability use count(distinct):

select name, count(distinct val) t unpivot (val col in (fielda, fieldb, fieldc, fieldd)) unpvt group name; 

however, efficient way keep processing on single row -- no joins or group bys or unions. assuming none of values null, following works 4 columns:

select name,        4 - ((case when fielda in (fieldb, fieldc, fieldd) 1 else 0 end) +             (case when fieldb in (fieldc, fieldd) 1 else 0 end) +             (case when fieldc in (fieldd) 1 else 0 end)            ) t; 

that is, start total count of columns. subtract 1 each time column column comes later. last condition ensures duplicates not counted more once.