Sql - same data column retrieval

Question

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 24-Feb-21 8:56am

John_Sampy

Updated 24-Feb-21 10:41am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2021-02-24T10:41:00

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[^]