Pivot Data by Month

Database

Pivot Data by Month

How would I make this result look like the second result?

STATE_IND           State_Count      Total     July       August        Month_

1 AL                           7,758            33,561    ?          7,758           August

2 AL                            8,747           33,561 8,747        ?               July

3 TN                            5,736           33,561  ?          5,736            August

4 TN                            11,320         33,561 11,320       ?               July

STATE_IND           State_Count      Total     July       August       

1 AL                             16,505       33,561   8747          7,758  

3 TN                              17, 056      33,561  11,320          5,736         

Select STATE_IND,

Count (State_IND) as State_Count,

Sum (state_Count) Over () as Total, 

(Case When MONTH_ = 'July' Then State_Count End) AS July,

(Case When MONTH_ = 'August' Then State_Count End) AS August,

 

Case Month_of_Year

When '8' Then 'August'

When '7' Then 'July'

End as Month_

  From Table 490

Inner Join TABLE 485 on Wire_Center = WIRE_CTR_CD Join SYS_CALENDAR.Calendar on CMTMT_DT= calendar_date

Where STATE_IND IN ('AL', 'TN')

and SRC_CD='SE'

and CMTMT_DT >='2012-07-25' and CMTMT_DT <=('2012-08-05')

and Area_NM_Turf like '%PCA'

and AskME_Job_CTGY = 'm'

and acct_TN = ' '

 Group by STATE_IND, Month_

3 REPLIES
N/A

Re: Pivot Data by Month

Can you please provide a DDL and insert statement for your test data?

It is not really clear if state_ind has the values '1 AL' etc.

And does the source table contain already the columns July and August?

At the end it should be straight forward aggregations. Lets assume state_ind has the format numberBLANKState

Select min(substr(state_ind,1, index(state_ind,' ')),

          substr(state_ind,index(state_ind,' ')+1 ) as state,

          sum(State_Count ),

          max(Total ),

          max(case when Month_ = 'July' then State_Count else null end) as July,

          max(case when Month_ = 'August' then State_Count else null end) as August

From ...

Re: Pivot Data by Month

Ulrich,

  The State_IND just has the State abbreviation i.e. AL, TN.   When i pasted it, it included 1, 2 etc, which is not part of the field. The source table only contains the CMTMT_Date and does not contain the columns July and August.  

Mainly just trying to get a total by state , by month with each State on one  row.  

I assume this would change the solution?  

Thanks for replying. 

N/A

Re: Pivot Data by Month

still no ddl and inserts...

but in this case it should be simply


Select state_ind,


          sum(State_Count ),


          max(Total ),


          max(case when Month_ = 'July' then State_Count else null end) as July,


          max(case when Month_ = 'August' then State_Count else null end) as August


From ...


group by state_ind


check the standard SQL aggregation documentation...