Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Cursor Help Please

    Hi all!

    I just need some help with cursors, a topic I don't profess to be an expert in.

    I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders".

    tblCustomers data:

    CustomerID Name
    1 Fred
    2 Charlie
    3 Lucy


    tblOrders data:

    OrderID CustomerId Qty
    1 1 10
    2 1 5
    3 1 20
    4 2 8
    5 3 20
    6 3 6


    I need to return a result set that puts all the "many" records into a single row, like:

    Name Qty

    Fred 10, 15, 20
    Charlie 8
    Lucy 20, 6

    THANKS IN ADVANCE!!!!!!!!!!

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Cursor Help Please

    Create this function


    CREATE Function ListOfQuantity (@ID Varchar(100))
    RETURNS Varchar(2000)
    As
    Begin
    Declare @List Varchar(2000)
    set @List=''
    Select @List = @List+RTrim(Qte) + ',' From Orders Where CustomerID =RTrim(@ID)
    Return(Left(@List,Len(@List)-1))
    End

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Cursor Help Please

    then you can do


    select name,dbo.ListOfQuantity(id)
    from customer

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Cursor Help Please

    What is magical is the

    Select @List = @List + Col1 From Tbl


    For a result that should be
    1
    2
    3

    it returns
    123

  5. #5
    Join Date
    Feb 2004
    Posts
    7

    Cool!

    I'll try it Karolyn, THANKS!

  6. #6
    Join Date
    Feb 2004
    Posts
    7

    It worked fine

    Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •