>SQL Select statements are not my strong point, so I could use a little help with this one. Perhaps someone can show me how to code this. Her is the problem:
>
>I have a parent and child table, and for this example I will call the two tables Parent and Child. The 2 tables are related by a field called id_field. The Parent table holds the first name and last name of the Supervisor, and the address of this office location (and there are an unlimited amount of these sales offices). The Child Table holds the First Name, Last Name, etc. of each Salesmen in a local sales office - and there is never more than 20 salesmen in an office, and they are only part of one office.
>
>What I need is a Select statement that will put a row in a cursor one per Sales Office that will also list the First and Last name of each salesmen in that office. As an example: I need it to have fields of:
>
>id_field
>FirstName (from Parent Table - is supervisor)
>LastName (from Parent Table - is supervisor)
>Address, city, State,zip (from Parent Table)
>FirstName1 (from Child Table, is the name of the first salesmen)
>LastName1 (from Child Table, is the name of the first salesmen)
>FirstName2 (from Child Table, is the name of the second salesmen)
>LastName2 (from Child Table, is the name of the second salesmen)
>etc. for 3rd salesman, 4th salesman, etc.
>
>One of the problems is some offices only have 2 or 3 salesmen, but none have more than 20. However, none might have 20 salesmen, but the max any office might have is 9 or 12, etc.
>
>Any help is appreciated. I figured I could write a routine and populate a table to come up with the required results, but I would prefer a Select statement that could do this (as the code would be shorter and quicker, and also I could learn something in the process).
>
>Any help is greatly appreciated.
>
>Mel Cummings


I think it's impossible to do it with SQL because you want to convert rows into columns, that's very difficult to do that with only SQL statements.