SQL Pivot Query for Student Details

Sonali Avhad

6y706.NET

i have query which display following output.

AdmissionNo FirstName LastName SubjectName

0001 Piya Sharma Sub1

0001 Piya Sharma Sub3

0001 Piy Sharma Sub5

but i want following output.

please tell me pivot query or any other option to display following output.

AdmissionNo FirstName LastName SubjectName Sub1 Sub2 Sub3 Sub4 Sub5

0001 Piya Sharma Sub1 Yes No No No No

0001 Piya Sharma Sub3 No No Yes No No

0001 Piya Sharma Sub5 No No No No Yes

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi sonali,
please refer this link:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
0
Amit Mohanty6y ago
Hey Sonali try below code
SELECT AdmissionNo,FirstName,LastName,
CASE WHEN [Sub1] = 0 THEN 'NO' ELSE 'YES' END AS Sub1,
CASE WHEN [Sub2] = 0 THEN 'NO' ELSE 'YES' END AS Sub2,
CASE WHEN [Sub3] = 0 THEN 'NO' ELSE 'YES' END AS Sub3,
CASE WHEN [Sub4] = 0 THEN 'NO' ELSE 'YES' END AS Sub4
FROM (SELECT AdmissionNo,FirstName,LastName, [SubjectName] FROM Tbl1 )Tab1
PIVOT
(
    Count(SubjectName) FOR [SubjectName] IN ([Sub1],[Sub2],[Sub3],[Sub4])
) AS Tab2
For more info please check the below links

https://www.c-sharpcorner.com/UploadFile/f0b2ed/pivot-and-unpovit-in-sql-server/
https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/
0