Thread: Combining multiple records on one row in Crystal

Folks, I'm fairly novice at Crystal, but have been using SQL for some time...I've searched the forums but none of the threads I've seen on this topic really have worked.

SELECT EmployeeId,
Case when Datepart(dw,WorkDate) = 1 Then WorkName Else 'X' End as "Sunday",
Case When Datepart(dw,WorkDate) = 2 Then WorkName Else 'X' End as "Monday",
Case when Datepart(dw,WorkDate) = 3 Then WorkName Else 'X' End as "Tuesday",
Case when Datepart(dw,WorkDate) = 4 Then WorkName Else 'X' End as "Wednesday",
Case when Datepart(dw,WorkDate) = 5 Then WorkName Else 'X' End as "Thursday",
Case When Datepart(dw,WorkDate) = 6 Then WorkName Else 'X' End as "Friday",
Case When Datepart(dw,WOrkDate) = 7 Then WorkName Else 'X' End as "Saturday"

FROM Work

WHERE WorkDate Between 20081201 AND 20081205

CURRENT OUTPUT

EmployeeId Monday Tuesday Wednesday Thursday Friday Saturday
1..................X..........X.............X..... .......1C..........X.......X
1..................X..........X.............X..... .......X..........17A.....X
2..................17A.......X.............X...... ......X............X......X
2..................X..........1C............X..... .......X............X......X
2..................X..........X.............3D.... .......X............X......X
2..................X..........X.............17A... .......X............X......X

DESIRED OUTPUT

EmployeeId Monday Tuesday Wednesday Thursday Friday Saturday
1..................X..........X.............X..... .......1C.........17A.......X
2..................17A.......1C...........3D...... ....X............X.........X
(continued row for 2).................17A............................ ......

(the 2 in parenthesis illustrates that I need to be able to show any value other than null for the employeeid on the given day)


I'm not real savvy with Crystal so I often put my SQL statements into the Command Table
from Database Expert which tends to be easier for me.

Does anyone have any suggestions for how to accomplish the desired output in Crystal XI?

Thanks!

Hi J,

Thanks for the reply. I don't think ISNULL could work from the query side because it won't compress rows.

Using ISNULL would return the same output that I'm getting now, except that it would show the "x"s as null values, but I'd still have separate rows for each record where there was at least one non-null value.

Hi J,

Right...I understand that using ISNULL(Workname,x) would return a value of X if Workname was a null value. That's essentially what is happening now.

I want to display "X" when there is now Workname value. So from that perspective, it's doing what I want it to do. The reason I wanted to use the SQL statement in Crystal is for report formatting purposes. Essentially, want I"m trying to do is show a weekly schedule. However, if I were to display a weekly schedule. Left left margin column should show Employeeid (which it does) and the column headers should display Employee Monday Tuesday Wednesday Thursday Friday Saturday; which they do.

So from that perspective so far, I could run this report in SQL without using a 3rd party report writing tool (Crystal).

Where I get caught up in is that I'm displaying multiple rows per employee due to duplication.

If you look at employeeid 1, he's scheduled to work for 2 days in the selected date range. But I don't want him to appear on 2 lines. I only want to view his information on one line (row). His info should look like this:
1..................X..........X.............X..... .......1C..........X.......X
1..................X..........X.............X..... .......X..........17A.....X


If you look at employeeid 2, he's scheduled to work 3 days in the same selected date range. However, on one of the days, he has two different assignments. So the current output shows him on 4 lines or row...but I would only want to show him on 2 rows. His information should look like this:


2..................17A.......1C...........3D...... ....X............X.........X
.............................................17A.. .......................... ......

PS...the dots are only there to help format this view


Thanks.

Awesome...I've tried some of the methods that have been posted on SQLTeams forums...this issue actually comes up a lot.

Most of the workarounds in SQL haven't worked for me. Mostly because I have a create table/create function restriction on my query writer...so I have to do it through select statements without setting up a function.

If you have ideas though...I'd be much appreciative!

Thanks!

Sorry J, for employeeid one in my 2nd to the last post...I said he should look like this:

1..................X..........X.............X..... .......1C..........X.......X
1..................X..........X.............X..... .......X..........17A.....X

That's incorrect...that's what he looks like now. It should look like this:

1..................X..........X.............X..... .......1C.........17A.......X


I'm sure you understood, but wanted to make sure I was clear.

Thanks again!