Displaying results of an SQL Pivot Query

[fdgsogc]

Hi,

Here's my problem statement: I'm using a query that uses the SQL Pivot function. But I'm having issues displaying the results.

Here's my query.

 <cfquery name="showprofileresults"  datasource="#db#" username="#dbusername#" password="#dbpassword#">
    select *
    from 
    (
      select pd.firstname + ' ' + pd.lastname as fullname,pr.participantemail,pr.meetingidnumber, pr.columnid,pd.firstname, pr.value
      from ProfileResults pr
      join participantdetails pd on pr.participantemail = pd.participantemail
      where pr.meetingidnumber = '#form.meetingnumber#' and pr.rowid not in(1,2,5,10,14,17)
    ) src
    pivot
    (
      sum(value)
      for columnid in ([1], [2], [3],[4])
    ) piv
 </cfquery>

I get the following results

participantemail		meetingidnumber		1	2	3	4
someemail@gmail.com		CBE95597		NULL	NULL	NULL	NULL
aguy@corpinc.com		[b][/b]CBE95597		33	36	39	12
test@domain.ca			CBE95597		28	36	30	26

I'm good with the results. The problem I am having is displaying the results in a <cfoutput> tag. Here's my display code.

  <cfoutput query="showprofileresults">
  <tr>
    <td>#currentrow#</td>
    <td>#participantemail#</td>
    <td>#fullname#</td>
    <td>#firstname#</td>
    <td>#1#</td>
    <td>#2#</td>
    <td>#3#</td>
    <td>#4#</td>
    <td>#2#-#4#</td>
    <td>#1#-#3#</td>
  </tr>
 </cfoutput>

And here are the results. I don't know how to refer to the pivot columns 1, 2, 3 and 4

 

[jdhilljr]

I haven't used the pivot function but I would suggest using a cfdump to see the structure of the data.

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim