CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    7

    [RESOLVED] Multiple Rows to a single row

    I have a DB with a many-to-one relationship and I would like the 'Many' to be on one line.

    In the example below Smith is a single Customer and is part of 4 regions.
    Now:
    Customer Region
    Smith A
    Smith B
    Smith C
    Smith D

    What I want:
    Customer Region
    Smith A,B,C,D

    Is this possible?

    Thanks

  2. #2
    Join Date
    May 2012
    Posts
    7

    Re: Multiple Rows to a single row

    Clarification: 'Customer' and 'Region' are two different fields.
    So I do not need them split up- I just need the Regions on one line seperated by Commas.

  3. #3
    Join Date
    Jul 2005
    Posts
    1,083

    Re: Multiple Rows to a single row

    Study the next solution, where we are concatenating a field and display the
    resulting string at group or report footer in only one line

    To do that, you have to create 3 formulas:

    The first one, let's call it @null:
    Code:
    whileprintingrecords;
    stringvar names := "";
    Place it in your GH section, it will return you null.



    The second one, let's say @comma_field:
    Code:
    whileprintingrecords;
    stringvar names;
    names := names & ", " & {table.field};
    Place it in your Detail section which you can suppress; the formula will return you next:
    , Jules, Christina, Paul


    and the 3rd formula, let's call it @final (or whatever you like):
    Code:
    whileprintingrecords;
    stringvar names;
    Mid(names, 3);
    Place it in your GF or report footer section.
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  4. #4
    Join Date
    May 2012
    Posts
    7

    Thumbs up Re: Multiple Rows to a single row

    Thank you so much, this worked great!

  5. #5
    Join Date
    Dec 2015
    Posts
    1

    Re: Multiple Rows to a single row

    Thanks. But, how can i clean the variable 'names' to show the records of the current group only?

  6. #6
    Join Date
    Jul 2005
    Posts
    1,083

    Re: Multiple Rows to a single row

    See Formula 1
    You must place it in the Group Header Section, in order to clean the variable
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured