CODEWITHSUNDEEP
sqlsql-servert-sql
Srinivasa Rao
Srinivasa Rao

Reputation: 172

SQL Group by Data conditional filtering

I have a table data as follows:

Customer    Level   Dept
AAA 1   Parent
AAA 2   Corporate
AAA 3   SmallBusiness
BBB 1   Parent
BBB 2   StateLevel
BBB 3   Education
BBB 4   RegionLevel
CCC 1   Parent
CCC 2   Sales
CCC 3   Healthcare
CCC 4   Online
CCC 5   Web

Conditions: If Level=2 and Dept=Corporate

User Department is Corporate

else if Level=3 and (Dept = Education or Dept = Sport)

User Department is Education

else if Level=2 and Dept=Sales && Level=3 and Dept=HealthCare

User department is HealthCare

DesiredOutput

Customer Department
AAA  Corporate
BBB  Education
CCC  Healthcare

Upvotes: 0

Views: 72

Answers (6)

John Cappelletti
John Cappelletti

Reputation: 81930

Another option is via PIVOT

Example

Declare @YourTable Table ([Customer] varchar(50),[Level] int,[Dept] varchar(50))
Insert Into @YourTable Values 
 ('AAA',1,'Parent')
,('AAA',2,'Corporate')
,('AAA',3,'SmallBusiness')
,('BBB',1,'Parent')
,('BBB',2,'StateLevel')
,('BBB',3,'Education')
,('BBB',4,'RegionLevel')
,('CCC',1,'Parent')
,('CCC',2,'Sales')
,('CCC',3,'Healthcare')
,('CCC',4,'Online')
,('CCC',5,'Web')

Select Customer
      ,Dept     = concat(
                         case when [2] in ('Corporate') then 'Corporate' end
                        ,case when [3] in ('Education','Sport') then 'Education' end
                        ,case when [2] in ('Sales') and [3]='Healthcare' then 'Healthcare' end
                        )
 From (
        Select *
         From  @YourTable
         Pivot (max([Dept]) For [Level] in ([2],[3]) ) p
      ) A

Returns

Customer    Dept
AAA         Corporate
BBB         Education
CCC         Healthcare

Upvotes: 2

mrbitzilla
mrbitzilla

Reputation: 398

According to your logic it goes like:

SELECT Customer,
    CASE WHEN Level=2 AND Dept='Corporate' THEN 'Corporate'
         WHEN Level=3 AND (Dept='Education' OR Dept='Sort') THEN 'Education'
         WHEN Level=2 AND (Dept='Sales' AND Level=3) AND Dept='HealthCare'
         END AS [Department] FROM [YourTable]

Upvotes: 0

Stan Shaw
Stan Shaw

Reputation: 3034

You actually aren't grouping at all here - you're creating a new column based on conditions of the existing columns. You want to use a CASE statement:

SELECT CASE WHEN Level = 2 AND Dept = 'Corporate' THEN 'Corporate' 
            WHEN Level = 3 AND (Dept = 'Education' OR Dept = 'Sport') THEN 'Education'
            WHEN (Level = 2 AND Dept = 'Sales') OR (Level = 3 AND Dept = 'HealthCare') THEN 'HealthCare'
            ELSE NULL END AS [Department]
FROM [YourTableName]
WHERE (Level = 2 AND Dept = 'Corporate')
OR    (Level = 3 AND (Dept = 'Education' OR Dept = 'Sport'))
OR    (Level = 2 AND Dept = 'Sales') 
OR (Level = 3 AND Dept = 'HealthCare')

Adjust your logic accordingly - the way your question was formatted it's difficult to know where the parenthesis should go - and they ARE important.

EDIT: Added a WHERE clause per @scsimon's suggestion.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a case when 

   select customer, 
        case when dept = 'Corporate' and level = 2 then 'Corporate'
             when ( dept =  'Education' or dept ='Sport' and level = 2 then 'Education'
             when  (level = 2 and dept ='Sales') and ( level=3 and dept = 'HealthCare' ) then 'HealthCare'
             else 'Unknow' end my_dept
  from table

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

Build something on these terms :

Case
when (level = 2 AND Dept = 'Corporate') Then Corporate
when (level = 3 AND Dept in ('Education','Sport)) Then Education
when (level = 2 AND Dept = 'Sales') Then Healthcare
when (level = 3 AND Dept = 'Healthcare') Then Healthcare
Else Null
End as col

Upvotes: 1

S3S
S3S

Reputation: 25112

Simple case statement should do it... and I changed the last logical clause to an OR since a row can't be level 2 and level 3...

select distinct
customer,
case 
   when Level=2 and Dept='Corporate' then 'Corporate'
   when Level=3 and (Dept='Education' or Dept='Sport') then 'Education'
   when (Level = 2 and Dept='Sales') or (Level=3 and Dept='Healthcare') then 'Healthcare'
end as 'Department'
from
   SomeTable
where
   Level in (2,3)

Upvotes: 2

Related Questions