Click here to Skip to main content
15,436,734 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team,

I have table A with data like below.

Stud#  Dept Status Marks DOB
100     ABC  FAIL   30   12-12-1990  
100     CDE  PASS   80   10-10-1995
101     XXX  FAIL   20   11-12-1990  
101     YYY  PASS   90   10-12-1985


For a Stud#, whenever Status is 'FAIL' for Dept# then would need to display data in below format. Please advise. Dept# to be separated as Main-Dept & Sub-Dept.

Result to be:
Stud#  Main-Dept Sub-Dept  Marks DOB
100       ABC        CDE   80   10-10-1995  
101       XXX        YYY   90   10-12-1985


What I have tried:

I have tried as using CASE condition like below, but its not appearing Sub-Dept as separate column.

SQL
SELECT A.STUD#, A.Dept, A.Marks, A.DOB, 
       CASE WHEN A.Status = 'FAIL' THEN NULL
            ELSE A.DOB
       END AS DOB
FROM A.Table
WHERE A.Stud# = '100'
Posted
Updated 24-Feb-21 10:41am
v2

1 solution

Check this:

SQL
SET DATEFORMAT dmy;

CREATE TABLE A
(
  [Stud#] varchar(30),
  Dept varchar(30),
  [Status]  varchar(30),
  Marks int,
  DOB  date
);

INSERT INTO A ([Stud#], Dept, [Status], Marks, DOB)
VALUES('100', 'ABC', 'FAIL', 30, '12-12-1990'),
('100', 'CDE', 'PASS', 80, '10-10-1995'),
('101', 'XXX', 'FAIL', 20, '11-12-1990'),
('101', 'YYY', 'PASS', 90, '10-12-1985');

SELECT [Stud#], MAX(Dept) AS Dept, MAX(SubDept) AS SubDept, MAX(Marks) AS Marks, MAX(DOB) As DOB
FROM
(
  SELECT [Stud#], Dept, NULL SubDept, 0 As Marks, DOB
  FROM A
  WHERE [Status] = 'FAIL'
  UNION ALL
  SELECT [Stud#], NULL Dept, Dept AS SubDept, Marks, DOB
  FROM A
  WHERE [Status] = 'PASS'
) Final
GROUP BY [Stud#]


Result:
Stud# 	Dept 	SubDept 	Marks 	DOB
100 	ABC 	CDE 	80 	1995-10-10
101 	XXX 	YYY 	90 	1990-12-11


SQL Server 2019 | db<>fiddle[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900