Displaying results of an SQL Pivot Query
Displaying results of an SQL Pivot Query
(OP)
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 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.
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
Talk To Other Members
RE: Displaying results of an SQL Pivot Query
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim