CODEWITHSUNDEEP
sqlsql-server
goofyui
goofyui

Reputation: 3492

SQL Query syntax for Pivot Table output

I have a table name PatientInsurance which has two columns Priority and PlanName.

Some patients have more than one insurance and some don't have.

I am fetching some records by joining the Patient Table and PatientInsurance Table. 

    From PatientInsurance Table :- Sample set of record for a specific patient.

    Priority      PlanName
    1             ADVANCE
    2             LOYALTY



Case when PatientInsurance.Priority = 1 , then output.PrimaryInsurance = PlanName
Case when PatientInsurance.Priority =2 , then output.SecondaryInsurance  = PlanName

Expected output as 

    PatientName PrimaryInsurance   SecondaryInsurance
     John        Advance             Loyalty
     Asif        BCBS 
     Merin       Advance              BCBS

How to write a query to display record in a single line output?

Added a sqlfiddle, Please

http://sqlfiddle.com/#!3/57d47/2

Upvotes: 1

Views: 111

Answers (2)

Erran Morad
Erran Morad

Reputation: 4753

Were you look for pivot query ? 

Select 
Pvt.PatientName, 
Pvt.[1] as PrimaryInsurance,
Pvt.[2] as SecondaryInsurance
From(
select P.PatientName , 
PIn.[Priority], 
PIn.PlanName
from Patient as P
inner join PatientInsurance as PIn
on P.Patid = Pin.PatId
) as normal  
PIVOT(
Max(PlanName)
For[Priority]
In([1],[2]) 
) as Pvt

It give me the output you have ask.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You don't need a pivot for that, you can use subqueries. You didn't specify the exact schema but it could be something like:

select PatientName,
     (select PlanName from PatientInsurance
          where PatientInsurance.PatientId = Patient.PatientId
          and PatientInsurance.Priority = 1) as PrimaryInsurance,
     (select PlanName from PatientInsurance
          where PatientInsurance.PatientId = Patient.PatientId
          and PatientInsurance.Priority = 2) as SecondaryInsurance
from Patient

Upvotes: 2

Related Questions