All Forums Database
tmcrouse 29 posts Joined 07/14
29 Jul 2016
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

dnoeth 4628 posts Joined 11/04
29 Jul 2016

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. 

Dieter

tmcrouse 29 posts Joined 07/14
29 Jul 2016

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;

You must sign in to leave a comment.