I'm hoping someone here can point me in the right direction on this one.
I'm fairly new to SQL, so maybe I'm just missing something easy.  I'm using
MS SQL Server 6.5.

I am trying to display a course schedule for a college.  I have a fairly
simple SELECT statement that gives me the following...

course_id    title        subtype    section    days    time
PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
PS101        Gen. Psych.    LECT    01    T           9:00am - 11:00am

The problem is that this is really only one class.  If course_id, title,
subtype, and section are the same for two consecutive records, I would like
to leave those fields blank on the second one.  I can't actually change
anything in the database, though.

Any suggestions?

P.S. Before Celko goes off, I'd just like to say that I didn't design this
database.  I'm just working with what I've got.  :)

Join the table to itself and only specifiy the column from the second table
where equal columns exist.

Jeff

 I am trying to display a course schedule for a college.  I have a
fairly imple SELECT statement that gives me the following...

 course_id    title        subtype    section    days    time
 PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
 PS101        Gen. Psych.    LECT    01    T       9:00am - 11:00am

 The problem is that this is really only one class.  If course_id,
title, subtype, and section are the same for two consecutive records, I
would like to leave those fields blank on the second one.  I can't
actually change anything in the database, though. <<

That is a report and not a query.  SQL is a query language, not a
report writer. You are also using reserved words for column names.
Having said that this is a dumb idea, here is one way to do it:

SELECT CS1.course_id, CS1.title, CS1.subtype, CS1.section,
       CASE WHEN CS1.course_days
                 = (SELECT MIN(course_days)
                      FROM ClassSchedule AS CS2
                     WHERE CS1.course_id = CS2.course_id
                       AND CS1.title = CS2.title
                       AND CS1.subtype = CS2.subtype
                       AND CS1.section = CS2.section)
            THEN CS1.course_days ELSE '  ' END,
       CASE WHEN CS1.scheduled_time
                 = (SELECT MIN(course_days)
                      FROM ClassSchedule AS CS2
                     WHERE CS1.course_id = CS2.course_id
                       AND CS1.title = CS2.title
                       AND CS1.subtype = CS2.subtype
                       AND CS1.section = CS2.section)
            THEN CS1.scheduled_time ELSE '  ' END
  FROM ClassSchedule AS CS1;

How do I know how bad the database was?  You never posted any DDL or
DML for me to see.  Nah, I am going bash you for the dumb query idea
instead <G> ...

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

   Thanks for the quick response!  Sorry, but I'm still missing the big
picture here.  Is there any way you could expalin it further or maybe give
me an example of how it would work?

Thank you.

Dave

Use a report generator. If you have Access, create an mdb with tables linked
to your SQL Server database, and then switch to the Reports tab and create a
report. There are options for suppressing repeated data. There are many
other report generators available. It is also fairly easy to do in VB by
looping through a recordset.