By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Merging 2 rows (moved from VB forum)

lotus18
100+
P: 866
Hello World

How to merge 2 rows in sql statement? I have this sample:
Expand|Select|Wrap|Line Numbers
  1. Subject Code     Time     Days     Room
  2. CSST 131          7-8AM     M        AVR
  3. CSST 131          7-8AM     TH        AVR
  4.  
(Merged)

Expand|Select|Wrap|Line Numbers
  1.  
  2. Subject Code     Time     Days     Room
  3. CSST 131        7-8AM     MTH             AVR
  4.  

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
Mar 20 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Mar 20 '08 #2

lotus18
100+
P: 866
OK. Thanks for your comment. -_-
Mar 20 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.