Returning results from multiple rows into one summary row

General
Enthusiast

Returning results from multiple rows into one summary row

Trying to figure out a query that pulls results from multiple rows into one row. For example, if I have an account that receives a monthly bill, and I want to know which months they received a bill, on one row, I wrote the following query:

 

SyntaxEditor Code Snippet

select acct_id, case when BILL_CYCL_RUN_MNTH_NBR = 1 then 1 else 0 end as January,case when BILL_CYCL_RUN_MNTH_NBR = 2 then 1 else 0 end as February,case when BILL_CYCL_RUN_MNTH_NBR = 3 then 1 else 0 end as March,
from Bill_table
where acct_id = '123456789'

The result I got is:

 

Acct_ID,January,February,March
123456789,1,0,0
123456789,0,1,0
123456789,0,0,1

 

The result I want is:

 

BAN,January,February,March
123456789,1,1,1

 

any help is appreciated!

 

1 REPLY
Teradata Employee

Re: Returning results from multiple rows into one summary row

Add a GROUP BY to combine the data into a single row, e.g.

 

select acct_id, max(case when BILL_CYCL_RUN_MNTH_NBR = 1 then 1 else 0 end) as January, max(case when BILL_CYCL_RUN_MNTH_NBR = 2 then 1 else 0 end) as February, max(case when BILL_CYCL_RUN_MNTH_NBR = 3 then 1 else 0 end) as March,
from Bill_table
where acct_id = '123456789'
group by acct_id;