Merging 2 rows (moved from VB forum)

[lotus18]

Hello World

How to merge 2 rows in sql statement? I have this sample:

Subject Code	 Time	 Days	 Room
CSST 131		  7-8AM	 M		AVR
CSST 131		  7-8AM	 TH		AVR

(Merged)

 
Subject Code	 Time	 Days	 Room
CSST 131		7-8AM	 MTH			 AVR

If they have the same subject code, time and room
then rows should be merged into a single row. Actually I want to print them using crystal report. Is there any CR's formula for this?

This my sample report

[Stewart Ross]

Hi Lotus18. What you ask is not really feasible in SQL. In effect you are asking to create a pivoted group within a row. The nearest Access comes to this is through crosstab queries which will pivot data from multiple rows, but these are not entirely suitable for what you want to do either, as the individual values pivoted become header columns for the whole query. Using a crosstab with the day value as the column heading you would end up with all days listed for all rows, with null entries in the unused day columns in each row.

There are programmed approaches which could do what you want, but these are fairly involved. When producing room timetables for lecturers I placed static unbound day fields in the footer of a group section of the Access report, then used the On Format event of the detail section (which was not otherwise used to display fields) to write the times for each class to the day elements in the footer. This gets quite involved, and I would suggest you accept what you have at the moment unless you are prepared to invest a fair amount of time in programming the reports to do what you require.

I think your report looks good as it is, and if it was me I'd leave it at that for now!

-Stewart

[lotus18]

OK. Thanks for your comment. -_-