Need SQL Function Brainwave

[gggram2013]

I have this function in sql server 2005:

ALTER FUNCTION dbo.fnGuestID()
RETURNS TABLE 
AS
RETURN SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice
       From Guest AS g, Sales As s 
       WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice' 
       GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.Date,s.Quantity,s.SalePrice

And I get the table below after i excute....What I would like to know or get some ideas on, is how to make the same GuestID, that comes up twice at the end, be on the same cells together one below the other instead of two separate cells...Let me know if this isn't clear.

[Shuja Ali]

I guess they are already in order.

Group By clause will also get them in order like Order By does.

Rr may be I misunderstood your question.

[jshultz]

I am not sure I am completely understanding you, but if I am...you can't get them "on the same cells" because you are also pulling their sale information, which is unique. If you were just pulling the guest information, you would be able to by using the distinct keyword.

[gggram2013]

Well something like that I would want because basically the Guest information should remain the same, can u show me an example if it were just the Sales table?

[gggram2013]

To Make it clearer, I would like GuestID '129", since it's the same Guest twice, I would like to retrieve the sales part of it together, so that when i print it...it shows the Guest with both sales on it...but so far I can't figure out another way or a way to do it.

[dglienna]

Guest 129 in a cell two cells high? Like MSHFlexgrid? Not sure what it's called in C#. MS Hierarchical Flex Grid

[gggram2013]

Hey well, I don't believe that it's possible for two separate rows to partially join because with the things I need to retrieve it will cause the rows to break...I think it's gonna be very tedious when it comes to me printing each value in its respective cell since the number of sales can become large and will therefore cause too much hazardous work, so what I'm gonna do is this:

"SELECT g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,
            sum(s.SaleTotal) as TotalSaleOfThisGuestID, count(*) as NumberOfSaleOfThisGuestID
            From Guest AS g, Sales As s 
            WHERE g.GuestID = s.GuestID AND s.SalesStatus = 'ToGuestInvoice'        
            GROUP BY g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail"

In which I still would like to obtain the date for each sale separate...and it comes to mind to create other columns where the other dates could be entered...I will try that and hopefully it works. Thanks for all ur suggestions, appreciate it

--GM