>>>>BTW, in LS how did you eliminate blanks? I guess I would have to dig your utility somewhere...
>>>
>>>Not sure what you mean by that.
>>
>>If I understood your suggestion correctly you were suggesting to have
>>
>>select LastName, FirstName, Phone, Type from People left join Phones on People.PersonID = Phones.PerrsonID
>>
>>In the report we'll have
>>
>>Last Name First Name Home Phone(s) Office Phone(s) Cellular
>>LastName FirstName Phone (Print When Type='Home') ...
>>
>>My question is - how dd you get all the phones on the same line using this approach ? I believe it would not work this way and all phones would end up on a new line per phone.
>
>Doesn't this seem like a crosstab to you?
Yes, it does. Though in my case since the number of possible types is limited and predefined I can get all the data into the cursor using few left joins and derived tables, e.g.
select p.LastName, p.FirstName, Home.HomePhone, ... from People p
left join
(select P.ID, Ph.Phone as HomePhone from People P
LEFT JOIN Phones Ph on P.ID = Ph.ID and Ph.Type = 'Home') Home
LEFT JOIN (...)
LEFT JOIN (...)
If it's not broken, fix it until it is.
My Blog