SQL Query to create view

[chrismcaniff]

Hi,
I have one table called comments that looks like this

recordno lineno comment
100 1 Joe Smith
100 2 Room 408

I cannot modify this table but would like to create a view which looks like this:

recordno guestname roomno
100 Joe Smith Room 408

Does anyone know how to write a SQL Query for This ?

 

[r937]

select recordno
     , max( case when lineno=1
                 then comment
             end ) as guestname
     , max( case when lineno=2
                 then comment
             end ) as roomno
  from comments
group
    by recordno

r937.com | rudy.ca