I am new to SQL Server.
I have a table:
Group id DEPT marks -------------------------------------- A 001 CS P A 002 ECE P A 003 MC P B 561 CS F B 781 IT F C 789 CS F C 003 CS F
If a department has failure ‘new column ‘ dept contain Failures should get value ‘F’
Here CS contains F so the entire dept should get a value ‘F’ in deptcontainsfailures column
where as other departments
eg:- ECE contains oly P , so it has ‘P’ in Ddeptcontainfailures
I am trying to do this select statement
SELECT group,
id,
dept,
marks,
CASE WHEN marks='P' THEN 'P'
ELSE 'F'
END Ddeptcontainfailure
FROM Depttable
The output I am getting
Group id DEPT marks Ddeptcontainfailures ----------------------------------------------------- A 001 CS P p A 002 ECE P P A 003 MC P P B 561 CS F F B 781 IT F F C 789 CS P p C 003 CS P p
How can I achieve that?
Desired output
Group id DEPT marks Ddeptcontainfailures ----------------------------------------------------- A 001 CS P F A 002 ECE P P A 003 MC P P B 561 CS F F B 781 IT F F C 789 CS P F C 003 CS P F
Advertisement
Answer
You can use MIN with partitioning to do this. This works because you have only two values for marks. If your real data contains other values you could use a case expression inside the MIN function.
declare @Something table
(
MyGroup char(1)
, id char(3)
, DEPT varchar(3)
, marks char(1)
)
insert @Something values
('A', '001', 'CS', 'P')
, ('A', '002', 'ECE', 'P')
, ('A', '003', 'MC', 'P')
, ('B', '561', 'CS', 'F')
, ('B', '781', 'IT', 'F')
, ('C', '789', 'CS', 'F')
, ('C', '003', 'CS', 'F')
select s.*
, min(marks) over(partition by DEPT)
from @Something s
order by s.MyGroup
, s.id
, s.DEPT