Crosstab query help in Teradata

Database

Crosstab query help in Teradata

I thought using a case statement would work for what I needed but it did not. I wanted to transpose but that is only in SAS as far as I know and I have a table with 888 million rows and that is just not going to work. 888 million because my KeyID is associated with multiple HM's

An example of the data have is:





KeyID HM
1111 COA Functional
1111 COA Pain
1111 COA Medicine
1111 CDC Neph
1111 CDC Eye
2222 COA Functional
2222 COA Pain
2222 COA Medicine
2222 CDC Neph
2222 CDC Eye

If I do a case statement, it just gives me









KeyID COAFunctional COAPain COAMedicine CDCNeph CDCEye
1111 1        
1111   0      
1111     1    
1111       0  
1111         0
2222 0        
2222   1      
2222     0    
2222       1  
2222         0

What I want for a crosstab query is it to look like this:









KeyID COAFunctional COAPain COAMedicine CDCNeph CDCEye
1111 1 0 1 0 0
2222 0 1 0 1 0
2 REPLIES
N/A

Re: Crosstab query help in Teradata

To pivot data you need to apply MAX to the CASEs like

MAX(case when HM = 'COAFunctional' then 1 else 0 end)

and add a final GROUP BY KeyId.

If KeyId is the PI of the table this should be effcient. 

Re: Crosstab query help in Teradata

Awesome. Thanks so much I got this to work and posted my code so it might be of help to others

create multiset table dl_qpt_cqe.medicare4 as

(select distinct MbrKey,

max(case when hedismeasure = 'Comprehensive Diabetes Care - CDC HBA1C GOOD' and compliantpopulation ='1' then 1 else 0 end) as CDCPoor,

max(case when hedismeasure = 'Comprehensive Diabetes Care - CDC NEPH' and compliantpopulation = '1' then 1 else 0 end) as CDCNeph,

max(case when hedismeasure = 'Comprehensive Diabetes Care - CDC EYE EXAM' and compliantpopulation = '1' then 1 else 0 end) as CDCEye,

max(case when hedismeasure = 'Controlling High Blood Pressure - CBP' and compliantpopulation = '1' then 1 else 0 end) as CBP,

max(case when hedismeasure = 'Colorectal Cancer Screening - COL' and compliantpopulation = '1' then 1 else 0 end) as COL,

max(case when hedismeasure = 'Adult BMI Assessment - ABA' and compliantpopulation = '1' then 1 else 0 end) as BMI,

max(case when hedismeasure = 'Adults Access to Preventive/Ambulatory Health Services - TOTAL' and compliantpopulation = '1' then 1 else 0 end) as Wellness,

max(case when hedismeasure = 'Breast Cancer Screening - BCS TOTAL' and compliantpopulation = '1' then 1 else 0 end) as BCS,

max(case when hedismeasure = 'Care of Older Adults - Functional Status Assessment - COA' and compliantpopulation = '1' then 1 else 0 end) as COAFunctional,

max(case when hedismeasure = 'Care of Older Adults - Medication Review - COA' and compliantpopulation ='1' then 1 else 0 end) as COAMedication,

max(case when hedismeasure = 'Care of Older Adults - Pain Screening - COA' and compliantpopulation = '1' then 1 else 0 end) as COAPain,

max(case when hedismeasure = 'Disease Modifying Anti-Rheumatic Drug Therapy in Rheumatoid Arthritis - ART' and compliantpopulation = '1' then 1 else 0 end) as RheumatoidART,

max(case when hedismeasure = 'Osteoporosis Management in Women Who had a Fracture - OMW' and compliantpopulation = '1' then 1 else 0 end) as Osteo

from dl_qpt_cqe.medicare3

group by mbrkey)with data;