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

RE: Displaying results of an SQL Pivot Query
If you can't stand behind your troops, stand in front of them.
Semper Fidelis
Jim