Solved

I need help with a T-SQL SELECT query.

Posted on 2014-04-11
3
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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