[SOLVED] SQL select column value as custom name

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!

Solution

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