Select values from Column into Rows? 1

[vikoch]

I join two table on tb.ID:
tb1 has few columns - tb1.id
tb1.category
tb1....
tb1....
tb2 has two columns - tb2.id
tb2.zip

For each ID in tb1 I have several records in tb2 with different value in tb2.zip.

I need to select all tb2.zip from tb2 for tb1.id.
But, I need place all tb2.zip on the report in the rows, not in the column.
Please help.
Thank you. Ira

 

[lbass]

You could insert a group on tb1.ID and then use a variable to collect the zips, as in:

//{@accum} for the detail section:
whileprintingrecords;
stringvar zip := zip + ", ";

//{@reset} for the group header:
whileprintingrecords;
stringvar zip;
if not inrepeatedgroupheader then
zip := "";

//{@display results} for the group footer:
whileprintingrecords;
stringvar zip;
if len(zip) >= 2 then
left(zip, len(zip)-2);

Drag the groupname into the group footer and then suppress the group header and detail section.

-LB

 

[vikoch]

HI LB,

I did what you suggested.

but, it is only diplayed (@display result) zip value from the last record from tb2. In detail section I can see that all records were selected.
Do you have any idea why it is happened?

Thank you.

 

[lbass]

Please show the exact formulas you used and explain in what report sections you placed them.

-LB

 

[vikoch]

This is real column name - TCIA_ZIP_Search.ZIPCODE


{@accum} - placed in the detail section

whileprintingrecords;stringvar zip := {TCIA_ZIP_Search.ZIPCODE} + ", ";

{@reset} - placed in the group header section

whileprintingrecords;stringvar zip;if not inrepeatedgroupheader then zip := "";

{@display results} - placed in the group footer section

whileprintingrecords;stringvar zip;if len(zip) >= 2
then left(zip, len(zip)-2);

Thank you

 

[lbass]

I can see why you were confused--my accum formula was incorrect. It should have been:

//{@accum} for the detail section:
whileprintingrecords;
stringvar zip := zip + {TCIA_ZIP_Search.ZIPCODE} + ", ";

Notice that "zip" is added to itself in order accumulate the string.

-LB

 

[vikoch]

Thank you very much. You are a lifesaver!!!!!!!!!!!!!!!!!!
Problem is solved.

 

[vikoch]

Hi LB,
I thought, I am done with this report, but I am not.

I need to list name colum from the third table tb3;

tb3.id
tb3.name

I join tb1.id = tb3.id

I also have several records from tb3 for tb1.id

I was trying to do same what you suggested (created varibales) previously for the tb3.name. But, it seems like tb2.zip is ovelaping the tb3.name and also displayed all tb2.zip for each tb3.name

Do you have any solution for this.
Thank you

 

[lbass]

Why would there be multiple names for one ID? What name do you expect to see? All of them? Please show a sample of how the report displays after adding the tb3. Does the zip string now contain duplicates?

-LB

 

[vikoch]

Hello LB,

Answer on your 1st question:

tb1.ID is a company ID and each company has several names(member name) on tb3. Also, each tb1.ID (company) has several zips on tb2.
All these three tables (tb1, tb2, tb3) have ID column as a key.

I need to display all tb3.name and tb2.zip in rows for tb1.id.

Answer on 2nd question:

My report shows all tb3.name(no dups) for each tb1.id and shows all tb2.zip for each tb3.name

Example -
-------------------------------------
id - 10
name1, name2, name3, name4

zip1, zip2, zip3, zip1, zip2, zip3, zip1, zip2, zip3, zip1, zip2, zip3
--------------------------------------

Thank you.

 

[lbass]

Change this formula:

//{@accum} for the detail section:
whileprintingrecords;
stringvar zip;
if not({TCIA_ZIP_Search.ZIPCODE} in zip) then
zip := zip + {TCIA_ZIP_Search.ZIPCODE} + ", ";

-LB

 

[vikoch]

Thank you very, very much. It seems like this problem is solved.