Solved

I need help with a T-SQL SELECT query.

Posted on 2014-04-11
3
220 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 142

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 142

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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