?
Solved

I need help with a T-SQL SELECT query.

Posted on 2014-04-11
3
Medium Priority
?
228 Views
Last Modified: 2014-04-11
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.
0
Comment
Question by:colinasad
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39993822
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39993826
if "not filled" means empty string, but not null:
 , case when Colour1  is null or rtrim(Colour1) = '' then 0 else 1 end
0
 

Author Closing Comment

by:colinasad
ID: 39993840
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question