Click here to Skip to main content
14,874,807 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[^]
   

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