[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

I need help with a T-SQL SELECT query.

Posted on 2014-04-11
3
Medium Priority
?
226 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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