Solved

Pivot table in ColdFusion

Posted on 2009-06-29
4
1,268 Views
Last Modified: 2012-05-07
I have this select statement:
<CFQUERY name="dataQuery_R1" datasource="#datasource#" result = "rptSQL">
   
SELECT 'ELECTRONIC' AS "MEDIA_TYPE"                                                  
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 1 AND 5 THEN 1 ELSE 0 END)    
    AS "ST_001_005"                                                  
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 1 AND 5 THEN A.Student_CNT ELSE 0 END)                  
     AS "ST_CUM_001_005"                                                  
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 6 AND 10 THEN 1 ELSE 0 END)    
     AS "ST_006_010"                                                  
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 6 AND 10 THEN A.Student_CNT ELSE 0 END)                        
     AS "ST_CUM_006_010"                                                

 ,SUM(CASE WHEN A.Student_CNT BETWEEN 11 AND 15 THEN 1 ELSE 0 END)
    AS "ST_011_015"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 11 AND 15 THEN A.Student_CNT ELSE 0 END)                        
    AS "ST_CUM_011_015"                                                
                                               
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 16 AND 20 THEN 1 ELSE 0 END)
    AS "ST_016_020"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 16 AND 20 THEN A.Student_CNT ELSE 0 END)                          
    AS "ST_CUM_016_020"  
                                               
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 21 AND 25 THEN 1 ELSE 0 END)
    AS "ST_021_025"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 21 AND 25 THEN A.Student_CNT ELSE 0 END)                        
    AS "ST_CUM_021_025"                                                
                                             
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 26 AND 30 THEN 1 ELSE 0 END)
    AS "ST_026_030"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 26 AND 30 THEN A.Student_CNT ELSE 0 END)                        
    AS "ST_CUM_026_030"                                                
                                               
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 31 AND 35 THEN 1 ELSE 0 END)
    AS "ST_031_035"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 31 AND 35 THEN A.Student_CNT ELSE 0 END)                          
    AS "ST_CUM_031_035"                                                
                                               
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 36 AND 40 THEN 1 ELSE 0 END)
    AS "ST_036_040"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 36 AND 40 THEN A.Student_CNT ELSE 0 END)                        
    AS "ST_CUM_036_040"        
                                                 
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 41 AND 45 THEN 1 ELSE 0 END)
    AS "ST_041_045"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 41 AND 45 THEN A.Student_CNT ELSE 0 END)                          
    AS "ST_CUM_041_045"                                                
                                               
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 46 AND 50 THEN 1 ELSE 0 END)
    AS "ST_046_050"                                                
 ,SUM(CASE WHEN A.Student_CNT BETWEEN 46 AND 50 THEN A.Student_CNT ELSE 0 END)                          
    AS "ST_CUM_046_050"                                                
                                                 
    FROM STUDENTS  A                                            
    WHERE STUDENT_STUS_CD = 'CMPLTE'
    AND  Student_YR = '2008';
</cfquery>
 I have result as one row, but I need to present this like:
Range       Stnt Cnt       Stnt Cumm       Count             Cumm
1 - 5              3,090       3,090       7,340             7,340
6 - 10       1,248       4,338       9,641             16,981
11 - 15           643       4,981       8,272             25,253
16 - 20          414       5,395       7,467             32,720
21 - 25         277       5,672       6,322             39,042
26 - 30         201       5,873       5,650             44,692
31 - 35        179       6,052       5,865             50,557
36 - 40       127       6,179       4,819             55,376
41 - 45       100       6,279       4,310             59,686
46 - 50        89       6,368       4,250             63,936
0
Comment
Question by:Albert3162
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 24742114
I would start by making a separate record for each value

This will give the number of records and the cumulative sum of the counts by a 5 number range

Then you can easily display them in any way..

select floor(A.Student_CNT/5) as RangeStart

     , count(a.ID)         as Qty

     , sum(a.Student_CNT)  as cumm

from STUDENTS  a 

group by floor(A.Student_CNT/5)

WHERE STUDENT_STUS_CD = 'CMPLTE'

AND   Student_YR = '2008'

and   A.Student_CNT > 0

Open in new window

0
 

Author Comment

by:Albert3162
ID: 24745037
good idea but not clear for me.
where you take (a.ID) as Qty, this column not exists
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24745090
substitute ID with whatever the primary key column name is for that table
0
 

Author Closing Comment

by:Albert3162
ID: 31597922
Good aproach
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need some regex help 15 27
looping over JSON Object and insert into query 3 57
problem with finding cfcomponent on cfselect bind 7 76
RETURN INFORMATION FROM COLDFSION CFC 1 63
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now