Conditional where; Sql query/TSql for SQLServer2008

Question

I really wanted to come up with the solution by myself for this one, but this is turning out to be slightly more challenging than I thought it would be.

The table I am trying to retrieve information would look something like below in simpler form.

Table: CarFeatures

+---+---+---+---+-----+
|Car|Nav|Bth|Eco|Radio|
+---+---+---+---+-----+
|a  |y  |n  |n  |y    |
+---+---+---+---+-----+
|b  |n  |y  |n  |n    |
+---+---+---+---+-----+
|c  |n  |n  |y  |n    |
+---+---+---+---+-----+
|d  |n  |y  |y  |n    |
+---+---+---+---+-----+
|e  |y  |n  |n  |n    |
+---+---+---+---+-----+

On the SSRS report, I need to display all the cars that has all the features from the given parameters. This will receive parameters from the report like: Nav-yes/no, Bth-yes/no, Eco-yes/no, Radio-yes/no.

For instance, if the parameter input were 'Yes' for navigation and 'No' for others, the result table should be like;

+---+----------+
|Car|Features  |
+---+----------+
|a  |Nav, Radio|
+---+----------+
|e  |Nav       |
+---+----------+

I thought this would be simple, but as I try to get the query done, this is kind of driving me crazy. Below is what I thought initially will get me what I need, but didn't.

select Car,
 case when @nav = 'y' then 'Nav ' else '' end +
 case when @bth = 'y' then 'Bth ' else '' end +
 case when @eco = 'y' then 'Eco ' else '' end +
 case when @radio = 'y' then 'Radio ' else '' end As Features
from CarFeatures
where (nav = @nav -- here I don't want the row to be picked if the input is 'n' 
or bth = @bth
or eco = @eco
or radio = @radio)

Basically the logic should be something like, if there is a row for every parameter that is 'yes,' list me all the features with 'yes' for that row, even though the parameters are 'no' for those other features.

Also, I am not considering to filter on the report. I want this to be on stored proc itself.

I would certainly like to avoid multiple ifs considering I have 4 parameters and the permutation of 4 in if might not be a better thing to do.

Thanks.

Answer 1

Your schema is awkward and denormalised, you should have 3 tables,

Car

Feature

CarFeature

The CarFeature table should consist of two columns, CarId and FeatureId. Then your could do something like,

SELECT DISTINCT
            cr.CarId
    FROM
            CarFeature cr
    WHERE
            cr.FeatureId IN SelectedFeatures;

Rant {

Not only would it be easy to add features without changing the schema, offer better performance because of support of set based operations covered by good indecies, overall use less storage because you no longer need to store the No values, you would comply with some well thought out and established patterns backed by 40+ years of development effort and clarification.

}

---

If, for whatever reason, you cannot change the data or schema, you could UNPIVOT the columns like this, Fiddle Here

SELECT
      p.Car,
      p.Feature
  FROM
      (
      SELECT
            Car,
            Nav,
            Bth,
            Eco,  
            Radio
        FROM
            CarFeatures) cf
  UNPIVOT (Value For Feature In (Nav, Bth, Eco, Radio)) p
  WHERE
      p.Value='Y';

Or, you could do it old style like this Fiddle Here,

SELECT
      Car,
      'Nav' Feature
  FROM
      CarFeatures
  WHERE
      Nav = 'Y'
UNION ALL
SELECT
      Car,
      'Bth' Feature
  FROM
      CarFeatures
  WHERE
     Bth = 'Y'
UNION ALL
SELECT
      Car,
      'Eco' Feature
  FROM
      CarFeatures
  WHERE
      Eco = 'Y'
UNION ALL
SELECT
      Car,
      'Radio' Feature
  FROM
      CarFeatures
  WHERE
     Radio = 'Y'

to essentially, denormalise into subquery. Both queries give results like this,

CAR    FEATURE 
A      Nav 
A      Radio 
B      Bth 
B      Radio 
C      Eco 
D      Bth 
D      Eco 
E      Nav 

Answer 2

--Aha! I kind of figured out myself (very happy) :). Since the value for the columns can only be 'y' or 'n,' to ignore when the parameters value are no, -- I will just ask it look for value that will never be there. --If anyone has a better way of doing it or enhancing what I have (preferred) would be appreciated. --Thanks to everyone who replied. Since this is a part of already existing table and also a piece of a big stored proc, I was reluctant to go with previous answers to the question.

--variable declaring and assignments here

select Car,
case when @nav = 'y' then 'Nav ' else '' end +
case when @bth = 'y' then 'Bth ' else '' end +
case when @eco = 'y' then 'Eco ' else '' end +
case when @radio = 'y' then 'Radio ' else '' end As Features
from CarFeatures
where (nav =    (case when @nav = 'y' then 'Y' else 'B' end 
                OR case when @bth = 'y' then 'Y' else 'B' end 
                OR case when @eco = 'y' then 'Y' else 'B' end 
                OR case when @radio = 'y' then 'Y' else 'B' end 
    )   

Answer 3

Try This, I believe this will solve your purpose.. 

SELECT   Car,  
     tblPivot.Property AS Features, 
     tblPivot.Value
     INTO #tmpFeature
 FROM     
 (SELECT CONVERT(sql_variant,Car) AS Car,CONVERT(sql_variant,NAV) AS NAV,         CONVERT(sql_variant,BTH) AS BTH,     CONVERT(sql_variant,ECO) AS ECO,  
CONVERT(sql_variant,Radio) AS Radio   FROM CarFeatures) CarFeatures  
 UNPIVOT (Value For Property In (NAV,BTH, ECO, Radio)) as tblPivot
 Where tblPivot.Value='y'


 SELECT 
  Car,
  STUFF((
    SELECT ', ' + Features 
    FROM #tmpFeature 
    WHERE (Car = Results.Car) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS Features
FROM #tmpFeature Results
GROUP BY Car

Answer 4

Try this its working ...........

declare @table table (Car char(1),Nav char(1),Bth char(1),Eco char(1),Radio char(1))
insert into @table 
select 'a',  'y' , 'n' , 'n',  'y'
union all
select 'b',  'n' , 'y' , 'n',  'n'
union all
select 'c',  'n' , 'n' , 'y',  'n'
union all
select 'd',  'n' , 'y' , 'y',  'n'
union all
select 'e',  'y' , 'n' , 'n',  'n'

select * from @table


select a.car,
Nav = left((case when a.nav = 'y' then 'Nav, ' else '' end)  +
 (case when a.bth = 'y' then 'Bth, ' else '' end)+
(case when a.Eco = 'y' then 'Eco, ' else '' end)+
 (case when a.Radio = 'y' then 'Radio,' else '' end),
 (len(((case when a.nav = 'y' then 'Nav, ' else '' end)  +
 (case when a.bth = 'y' then 'Bth, ' else '' end)+
(case when a.Eco = 'y' then 'Eco, ' else '' end)+
 (case when a.Radio = 'y' then 'Radio,' else '' end)))-1))
from @table a