CODEWITHSUNDEEP
sqlsql-servert-sqlcasehl7-cda
Chris
Chris

Reputation: 3

SQL select column value as custom name

I'm trying to pull out data into a new column if another column equals a particular value.

My data is different telecommunications values stored into the same column with another descriptor column specifying if it's an Email address or home phone, mobile, etc. I want to pull email into an email column, homephone into a homephone column, and mobile into it's own column, etc.

I've started with CASE, but am not sure how to expand that to use the value in the 2nd column:

select TOP 20 
    BF.fileID,  
    PT.Patient_DateOfBirth as DOB, 
    ContactType = 
        CASE CONT.Patient_Telecom_Use
            WHEN 'EM' THEN 'Email'
            WHEN 'HP' THEN 'HomePh'
            ELSE 'otherContact'
        END 
-- 'EM' is email, 'HP' is home phone, etc, need to figure out how to select CONT.Patient_Telecom_value into 'email' etc
    from  
        [BaseFile] BF  
    INNER JOIN [Patient] as PT 
        ON  BF.[fileId] = PT.[FileId]
    INNER JOIN [PatientTelecomunication] as CONT
        ON BF.[fileId] = CONT.[FileId]

If I were writing this as a hypothetical IF-THAN statement, I'd say:

IF 
Patient_Telecom_Use='EM' 
THEN select Patient_Telecom_value as 'email'

Thanks!

Upvotes: 0

Views: 2410

Answers (1)

GMB
GMB

Reputation: 222682

You seem to want conditional aggregation. The logic is to move the conditional expressions within aggregate functions for each column to pivot, and put the other columns in a GROUP BY clause, like so:

select TOP (20)
    BF.fileID,  
    PT.Patient_DateOfBirth as DOB, 
    MAX(CASE WHEN CONT.Patient_Telecom_Use = 'EM' THEN CONT.Patient_Telecom_value END) as Email,
    MAX(CASE WHEN CONT.Patient_Telecom_Use = 'HP' THEN CONT.Patient_Telecom_value END) as HomePh
FROM [BaseFile] BF  
INNER JOIN [Patient] as PT ON  BF.[fileId] = PT.[FileId]
INNER JOIN [PatientTelecomunication] as CONT ON BF.[fileId] = CONT.[FileId]
GROUP BY BF.fileID,  PT.Patient_DateOfBirth

Upvotes: 0

Related Questions