How to format leave detail into tabular/pivot format?

Hello Expert!

I need help to I translate this data...

Table "LeaveDetail"

StaffNo | StartDate | EndDate | LeaveType |
1 | 23/04/2006 | 26/04/2006 | AL |
2 | 24/04/2006 | 25/04/2006 | MC |
3 | 26/04/2006 | 27/04/2006 | EL |
1 | 30/04/2006 | 02/05/2006 | EL |

Into this format...

|Apr|Apr|Apr|Apr|Apr|Apr|Apr|Apr|May|May|May|May|
StaffNo |23 |24 |25 |26 |27 |28 |29 |30 |01 |02 |03 |04..

1 |AL |AL |AL |AL | | ... |EL |EL |EL |
2 | |MC |MC | | |
3 | | | |EL |EL |

Parameter:
Date From e.g. 23/04/2006 to 23/05/2006

Using only query statement...

Is this possible?

TIA

Regards.

WOuld be very heavy query, do you have a calendar table to join to ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||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.|||

Hmm..

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

|||

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?

|||

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

|||

Thanks, I'll try

Regards