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.
I get the following results
I'm good with the results. The problem I am having is displaying the results in a <cfoutput> tag. Here's my display code.
And here are the results. I don't know how to refer to the pivot columns 1, 2, 3 and 4
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.
Code:
<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
Code:
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 26I'm good with the results. The problem I am having is displaying the results in a <cfoutput> tag. Here's my display code.
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