How to format leave detail into tabular/pivot format?

How to format leave detail into tabular/pivot format?

• Phil

  I forgot to mention I’m using MS SQL Server 7

  I found similar solution in MS Access. All done with 1 Table for days + query to convert to pivot/crosstab format, no coding in client side and no formatting in reporting tool needed

  e.g.

  TblDay e.g.: 1,2,3,4....31

  The query:

  PARAMETERS [Enter Month] Text ( 255 ), [Enter Year] Text ( 255 );

  TRANSFORM First(LeaveDetail.LeaveType) AS FirstOfLeaveType

  SELECT EmpNo
  FROM tblDays, LeaveDetail
  WHERE DateSerial([Enter Year],[Enter Month],[Day])) Between [LeaveDetail].[dStartDate] And [LeaveDetail].[dEndDate]
  GROUP BY EmpNo
  ORDER BY EmpNo

  PIVOT tblDays.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

  The result will look like this:

  StaffNo |1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12..
  ---------------------------------------------------------
  1 |AL |AL |AL |AL | | ... |EL |EL |EL |
  

  I know there are no such functions for tabular format in SQL Server 7, so I’m here to find similar solution that producing result as I have mention earlier which not depend on client or reporting tool

  Is this possible

  
  

• Chris Powell

  Hmm..

  No I don' have, appreciate if you can guide me on that

  
  

• MathiasWestin

  this thing looks like a cross tab queries.

  maybe some examples from here will help.

  http://www.sqlmag.com/Article/ArticleID/15608/15608.html

  pls see the zip files

  here some more

  http://www.sqlteam.com/item.asp ItemID=2955

  you have do it thru dynamic query

  
  
  

• Nihar

  WOuld be very heavy query, do you have a calendar table to join to
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• Guino

  You could do it in a SELECT statement or using PIVOT operator in SQL Server 2005. But there is no dynamic aspect for the query i.e., the names of the columns (Apr 23, Apr 24) should be hard-coded in the SELECT statement unless you generate the column names at run-time and use dynamic SQL to execute the query. So your options are fairly limited. On the other hand, this type of pivot operation is a breeze to do on the client side. Any reporting tool wll handle this without a problem. So if it is a one-time affair then you can write a SELECT statement to get the expected results. Otherwise you will have to use a solution that is easy to maintain and extend based on what I described above.
  
  

• Fendy

  Thanks, I'll try

  Regards

  
  
• How to format leave detail into tabular/pivot format?