Outputting data in Excel

[jenn1]

I’m exporting data to Excel.  I have some outputting issue.  If a user has taken a class, I would like to put an X in the cell.  I have to get all the users by running the “getsUsers” query first to get be all the rows of users.  Then, I would have to run “getClassesUsersHaveSignedUp4” within “getsUsers”.  This unfortunatley gives me the first row of users. 

I would like to display the data like this:

Users	Biology	Chemistry	Math	Phys
Tommy	X		X	X
Kim		X		X
Sara			x

<CFQUERY name="getUsers" datasource="#dsn#">

                SELECT     userid, class_id, name

                FROM     userstable

</CFQUERY>

<CFQUERY name="getClassesUsersHaveSignedUp4" datasource="#dsn#">

                SELECT     class_id, userid class

                FROM      class_junction

                WHERE   userid = #userid#

</CFQUERY>

<table width="100%" border="1" cellspacing="0" cellpadding="0">

<tr>

<td valign="top">User Name</td>

<CFQUERY name="getClasses" datasource="#dsn#">

                SELECT     class_id, userid , class

                FROM      lookup_table

                WHERE   class_id = #class_id#

</CFQUERY>

<cfoutput query=” getClasses “>

<td valign="top"> #class#</td>

</cfoutput>

</tr>

<cfoutput query =” getUsers”>

<tr>

<td valign="top">#name#</td>

<td valign="top”> <cfif a user has taken a class> X </cfif> </td>

</tr>

</cfoutput>

</table>

[Dan_Bracuk]

This is not a short answer question.  Eventually you will want a cross tab query but my answer will be limited to getting the data you need in a normal format.  Something I am going to ignore in my query, but you have to address is the class_id field in the usertable and the userid field in your lookup_table.  They seem to defeat the purpose of having the class_junction table which I assume sets up your many to many relationship.

This query gets you started

select class, name

from lookup_table lt left join class_junction cj on lt.class_id = cj.class_id

and maybe some other constraints from class_junction

left join userstable u on cj.userid = u.userid

and maybe some other contstraints from userstable

where etc

[JR__Bob__Dobbs-qSBHQ2]

Jenn,

Options:

1. You may want to post this question in a forum specific to your database version.  For example, in Microsoft SQL Server or Oracle you might use the PIVOT feature.

2. Use a subquery, if your database supports them.  See sample below for Microsoft SQL Server, I have not tested the query.  The problem with this approach is needing to define the columns, in this case one for each class, when you write the query instead of having your database create the columns dynamically.  It may also have slow performance due to having to execute sub queries for each row in the resultset.

SELECT
     U.name AS name,
     
     CASE 
          WHEN EXISTS 
                    ( 
                         SELECT * 
                         FROM class_junction AS J 
                                        INNER JOIN lookup_table AS L ON ( J.class = L.class ) 
                         WHERE J.userid = U.userid
                              AND J.class = 'Biology'
                    )
               THEN 'X'
          ELSE ''
     END AS Biology,

     CASE 
          WHEN EXISTS 
                    ( 
                         SELECT * 
                         FROM class_junction AS J 
                              INNER JOIN lookup_table AS L ON ( J.class = L.class ) 
                         WHERE J.userid = U.userid
                              AND J.class = 'Math'
                    )
               THEN 'X'
          ELSE ''
     END AS Math

FROM userstable AS U