6 Replies Latest reply on Jun 8, 2010 3:50 PM by Dan Bracuk

    reading dynamic sql columns in coldfusion

    funandlearning333 Level 1

      I have a query which has static as well as dynamic columns. something like below:

      <cfset vMarks = "marks1, marks2, marks3">
      <cfquery name="querymarks" datasource = "abc">

      SELECT firstname, lastname,
      <cfloop from="1" to="listlen(vMarks)" index="index">
          marks_#index# <cfif #index# NEQ listlen(vMarks)>,</cfif>
      </cfloop>
      </cfquery>

      The query result set will look like below:

      firstname lastname  marks1 marks2   marks3 ...
      abc        abc         112        113        114
      def        def           121        122        123

      So when I am using coldfusion to display the above resultset I am doing the below:


      <table>
        <tr>

          <td>FIRST NAME</td>
          <td>FIRST NAME</td>
          <td>FIRST NAME</td>

        </tr>
        <cfoutput query="querymarks">
        <tr>

          <td>querymarks.firstname</td>
          <td>querymarks.lastname</td>
          <cfloop from="1" to="listlen(vMarks)" index="index">
           <td>querymarks.marks_#index#</td>
          </cfloop>
        </tr>
        </cfoutput>
      </table>

      The number of marks column is dynamic but I have a variable which stores the list of marks. I am facing problem in displaying the marks with coldfusion. Can anyone let me know if this can be done?