Transact SQL - Pivot in select statement

Question

I have 3 tables from which I am building a view.

1. Patients - has list of basic patient details
   
2. Patient_DiseaseFindings_EntryList - is a separate dt when a patient disease is register the PatientID (FK)from Patients table , along with disease related data is entered
   
3. ExternalDiseasesList - is another table with a menu of disease.
   

Below is the query to build the Patients Overview view

/****** Script for SelectTopNRows command from SSMS  ******/  
SELECT   
      PE.[PatientID]  
	  ,[Year]  
      ,[Season]  
      ,[Serial]  
	  ,[Age]  
      ,[Length]  
	  ,PDF.[Name] as [Disease Finding]  
      ,[DoctorName]  
      ,[Comments_Lab]  
      ,[SampleID]  
      ,[LabAssistant]  
FROM [dbo].[Pantient] PE  
   , [dbo].[Patient_DiseaseFindings_EntryList] PDF  
   , [dbo].ExternalDiseasesList EDL  
WHERE PE.PatientID = PDF.PatientID  
   and PDF.DiseaseFindingID = EDL.DiseaseFindingID  
Group By   
   PE.[PatientID],[Year],[Season],[Serial],[Age],[Length],PDF.[Name],[DoctorName],[Comments_Lab],[SampleID],[LabAssistant]  

I get the output but for each disease I get individual line

But I need the output in one line for each patient with each disease as a column from the ExternalDiseasesList. If the patient has that disease then out Yes else No.

So my query must do a pivot view /Transposed. How should I select to make the pivot?

Your guidance will help. Thank you.
NewB

Answer 1

SELECT 
       PE.[PatientID]
       ,[Year]
       ,[Season]
       ,[Serial]
       ,[Age]
       ,[Length]
       ,IIF(EXISTS (SELECT *
                    FROM   [dbo].[Patient_DiseaseFindings_EntryList] PDF
                    JOIN   [dbo].ExternalDiseasesList EDL ON PDF.DiseaseFindingID = EDL.DiseaseFindingID
                    WHERE  PE.PatientID = PDF.PatientID
                      AND  PDF = 'Eye, Damaged'), 'Yes', 'No') AS "Eye, Damaged"
       ,IIF(EXISTS (SELECT *
                    FROM   [dbo].[Patient_DiseaseFindings_EntryList] PDF
                    JOIN   [dbo].ExternalDiseasesList EDL ON PDF.DiseaseFindingID = EDL.DiseaseFindingID
                    WHERE  PE.PatientID = PDF.PatientID
                      AND  PDF = 'Skin Rashes'), 'Yes', 'No') AS "Skin Rashes"
        --- Et cetera
       ,[DoctorName]
       ,[Comments_Lab]
       ,[SampleID]
       ,[LabAssistant]
 FROM [dbo].[Pantient] PE

If you tell me that you want the list of diseases to be dynamic, you should give this a second thought of whether you actually want to do this in the data layer. This is often better handled in the presentation layer. To do it dynamically in the data layer, you need dynamic SQL and that raises the stakes considerably.

Answer 2

Please check:

CREATE TABLE Patients(PatientID INT,[Year] INT,Season INT,Serial INT,  
Age INT,[Length] decimal(3,2),Doctor varchar(15),  
Comments varchar(15),Sampleid INT,Lab varchar(15))  
INSERT INTO Patients VALUES(123248,2018,1,1228,3,3.75,'Saas','Test'  
,1054,'Kristian'),  
(123249,2018,1,1229,4,5,'Aug','Refer',1055,'Kristian'),  
(123247,2018,1,1228,3,3.75,'Saas','Test',1054,'Kristian'),  
(123247,2018,1,1228,3,3.75,'Saas','Test',1054,'Kristian')  
  
CREATE TABLE Patient_DiseaseFindings_EntryList  
(EntryUID INT,PatientID INT,DiSeaid INT,  
[Name] varchar(15))  
INSERT INTO Patient_DiseaseFindings_EntryList VALUES  
(1,123247,64,'Eye,Damaged'),  
(2,123247,65,'skin'),  
(3,123248,65,'skin'),  
(4,123249,61,'Retina'),  
(5,123250,61,'Retina')  
  
CREATE TABLE ExternalDiseasesList   
(DiSeaid INT,[Name] varchar(15),Active INT)  
INSERT INTO ExternalDiseasesList  VALUES  
(61,'Retina',1),  
(62,'Hemorrage',1),  
(63,'Bulging',1),  
(64,'Eye,Damaged',1),  
(65,'skin',1)  
  
SELECT * FROM Patients  
SELECT * FROM Patient_DiseaseFindings_EntryList  
SELECT * FROM ExternalDiseasesList  
  
;WITH cte  
as(SELECT P.[PatientID],[Year],[Season],[Serial],[Age],[Length]  
       ,e.[Name] as [Disease Finding]  
FROM ExternalDiseasesList e  
LEFT JOIN  [Patient_DiseaseFindings_EntryList] pd  
ON  e.DiseaID = pd.DiseaID  
LEFT JOIN  Patients p  
ON p.PatientID = PD.PatientID  
Group By   
    p.[PatientID],[Year],[Season],  
 [Serial],[Age],[Length],pd.[Name],  
 e.[Name])  
  
SELECT [PatientID],[Season],[Serial],[Age],[Length]  
,CASE WHEN Retina IS NULL THEN 'No' ELSE 'Yes' END Retina,  
CASE WHEN skin IS NULL THEN 'No' ELSE 'Yes' END skin,  
CASE WHEN [Eye,Damaged] IS NULL THEN 'No' ELSE 'Yes' END [Eye,Damaged],  
CASE WHEN Bulging IS NULL THEN 'No' ELSE 'Yes' END Bulging ,  
CASE WHEN Hemorrage IS NULL THEN 'No' ELSE 'Yes' END Hemorrage  
FROM (SELECT * FROM cte) c  
PIVOT (MAX([Year]) FOR [Disease Finding]   
IN(Retina,skin,[Eye,Damaged],Bulging,Hemorrage)) P  
WHERE [PatientID] IS NOT NULL  
ORDER BY [PatientID] DESC  
  
  
DROP TABLE Patients  
DROP TABLE Patient_DiseaseFindings_EntryList  
DROP TABLE ExternalDiseasesList  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.