I need help with a T-SQL SELECT query.

Can anyone help me with a T-SQL query?

Using simplified column names, I have a SQL Server Table containing Stock Codes with up to 12 Colours the stock item is available in, eg

StkCol_StockCode nvarchar(20),
StkCol_Colour1 nvarchar(20),
StkCol_Colour2 nvarchar(20),
StkCol_Colour3 nvarchar(20),
StkCol_Colour4 nvarchar(20),
all the way down to
StkCol_Colour12 nvarchar(20)

I would like to produce a T-SQL SELECT query that returns the Stock Code and the number of non-blank colour columns.
ie quickly seeing how many colours have been filled out for each stock item.

I cannot assume that the 12 "colour" values have been filled out without any gaps (ie colours 1, 2, 3 might be filled out, 4, 5, 6, 7 might be blank, but 8, 9, 10, 11, 12 might also contain values).

What syntax can I use to create a "count" of  the non-blank colour columns for each record in the table?

Many thanks.
colinasadAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select ...
  , case when Colour1  is null then 0 else 1 end
  + case when Colour2  is null then 0 else 1 end
 + case when Colour3  is null then 0 else 1 end
  ... etc ...
    as ColorCount
   from yourtable
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if "not filled" means empty string, but not null:
 , case when Colour1  is null or rtrim(Colour1) = '' then 0 else 1 end
0
 
colinasadAuthor Commented:
Thanks for the super-fast solution.
Works perfectly; and so simple when you know how and get into the correct frame of mind.
Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.