mardi 5 octobre 2021

Transact SQL - Pivot in select statement

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

  1. Patients - has list of basic patient details enter image description here
  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 enter image description here
  3. ExternalDiseasesList - is another table with a menu of disease.
  4. enter image description here

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 enter image description here

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? enter image description here

Please guide. NewB

Aucun commentaire:

Enregistrer un commentaire