Cross Tab result set in SQL

• Ian Frank Lindsay

  Grasshopper

  Points: 12

  More actions

  May 25, 2006 at 10:56 am

  #172581

  I want to dynamically count how many applications I have had from all my introducers by month/year but only showing the last 13 months applications.

  This will be an ongoing monthly report & the output will be in Crystal reports

  The final output should have introducers down the left hand side, 13 columns of integers detailing each month/year count and a 14 column showing the total which would be the sum of all 13 months applications.

  Lastly there should be a total for all 14 columns at the end of the report.

  Can anyone help please.

   

   

   

• lakusha

  SSCommitted

  Points: 1857

  More actions

  May 25, 2006 at 12:51 pm

  #640148

   

  This looks like a simple cross-tab report. This is solved by a simple SUM(CASE). Search for cross tab or cross-tab and you should find a zillion examples on this site (and others). To receive a specific answer, please supply DDL.

  With SQL2k5, look at the PIVOT join which is an alternative (but not necesarily better, just different).

• Marshall Smith

  SSCrazy

  Points: 2041

  More actions

  May 30, 2006 at 7:37 am

  #640673

  I strongly recommend that you create this with the cross-tab functionality built into Crystal Reports. It is simpler, more powerful, and more intuitive.

• Prakash Sawant

  SSCrazy

  Points: 2173

  More actions

  May 31, 2006 at 3:00 am

  #640850

  If the numbers of columns are going to be constant at all the time then cross tab is the most effective way of getting the result set.

   

  ------------
  Prakash Sawant

  http://psawant.blogspot.com

• Ian Frank Lindsay

  Grasshopper

  Points: 12

  More actions

  May 31, 2006 at 3:05 am

  #640851

  Many Thanks, I indeed built the report using a Crystal Cross-tab & everything worked out just fine.

  Thanks to all who advised.