SQL Server distinct query with two columns in same table

user_7260158 0
Viewed 70

My tables are:

tblProduct 

male| female| Brand_ID

and

tblBrands 

Brand_ID| Brand_Name

My query is here:

Select distinct   
    a.Male, a.Female, b.Brand_Name 
from 
    tblProduct as a 
inner join
    tblBrands as b on b.Brand_ID = a.Brand_ID

This query returns this output:

Male    Female  Brand_Name
----------------------------------
 1       0        ABC
 0       1        ABC
 1       0        ABC
 1       0        PQR
 1       0        PQR
 0       1        PQR
 0       1        XYZ
 0       1        XYZ

But I want this output shown below - what should I do for to get this?

Male    Female  Brand_Name
 ----------------------------------
 1       1        ABC
 1       1        PQR
 0       1        XYZ

There check is if the brand XYZ is for male or female if is a male bit is true and female bit is true is show XYZ 1 1 or if female or male present according to that show XYZ 1 0 or XYZ 0 1

tsql sql-server sql
2 Answers

A simple GROUP BY clause will solve your problem

Select   
  Max(cast(a.Male as int)) as Male  , 
  Max(cast(a.Female as int)) as Female,
  b.Brand_Name 
from tblProduct as a inner join
tblBrands as b on b.Brand_ID = a.Brand_ID
group by b.Brand_Name
user_11232260

Since you mentioned that Male and Female columns are of type bit then, this is how you can write your query without needing MAX and CAST functions:

 select (case when ((select count(*) from tblProduct a where a.Male=1 and a.Brand_ID=b.Brand_ID) > 0) then 1 else 0 end) Male,
        (case when ((select count(*) from tblProduct a where a.Female=1 and a.Brand_ID=b.Brand_ID) > 0) then 1 else 0 end) Female
        ,b.Brand_Name
 from tblProduct as a inner join
 tblBrand as b on b.Brand_ID = a.Brand_ID
 group by b.Brand_Name,b.Brand_ID
user_11625390
Related