Here's my first crack at it, there might be something better (assumes field width of 20 on lup_value):
select c1.order_key, max(iif(c1.lup_type = "make", c1.lup_value, space(20))) as make, ;
max(iif(c1.lup_type = "color", c1.lup_value, space(20))) as color ;
from cars c1 ;
left join cars c2 on (c1.order_key = c2.order_key and ;
c1.lup_type <> c2.lup_type ) ;
group by c1.order_key
>I have a cursor with 3 fields order_key, lup_type, lup_value.
>The data might look as follows:
>531 make FORD
>531 color BLUE
>532 make HONDA
>532 color RED
>533 color ORANGE (person didn't enter a car make)
>534...etc.
>
>These values were already retrieved from a vehicle_info table and a lookup table.
>
>How can I take these values and combine them so that it will have the following layout:
>
>order_key, veh_make, veh_color
>531, FORD, BLUE
>532, HONDA, RED
>533, , ORANGE
>
>I tried a self join but can't get the list to show unique order_key's.
>
>As always, thanks for your excellent help.
>
>Marcel
Steve Gibson