BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Using Enterprise Guide, Combine Rows in results

I have a table with a one-to-many design.  I'd like to convert it to a one-to-one table.  See below:

What I have

 CUSTOMER MODE COLOR 12345 CAR BLUE 12345 CAR RED 12345 CAR WHITE 98765 BIKE BLUE 98765 BIKE RED

What I want

 CUSTOMER MODE COLOR 12345 CAR BLUE,RED,WHITE 98765 BIKE BLUE,RED

Is there a way I can accomplish this in  EG?  Coding isn't my forte 😉

Thanks for any help you can provide!

Rita Yee

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: Using Enterprise Guide, Combine Rows in results

Learned how to run a program to accomplish what I wanted!

data OUTPUT_NEW (drop=cage_reason_cd);
set OUTPUT;
by employee_nbr;
length cage_reason_cd_new \$200;
retain cage_reason_cd_new;
cage_reason_cd_new=ifc(first.employee_nbr,cage_reason_cd,catx(',',cage_reason_cd_new,cage_reason_cd));
if last.employee_nbr then output;
run;

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
2 REPLIES 2
Super User

## Re: Using Enterprise Guide, Combine Rows in results

1. Use a Transpose task to flip your data to a wide format.

2. Use Query Builder and a computed column with the CATT function to concatenate all the results into one field.

Here are coding solutions by the way, if you run the last option, it will be what I'm suggesting you do, except you can use Tasks to do it. You can run the code and test it - check the intermediary tables as well.

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

@RPYee wrote:

I have a table with a one-to-many design.  I'd like to convert it to a one-to-one table.  See below:

What I have

 CUSTOMER MODE COLOR 12345 CAR BLUE 12345 CAR RED 12345 CAR WHITE 98765 BIKE BLUE 98765 BIKE RED

What I want

 CUSTOMER MODE COLOR 12345 CAR BLUE,RED,WHITE 98765 BIKE BLUE,RED

Is there a way I can accomplish this in  EG?  Coding isn't my forte 😉

Thanks for any help you can provide!

Rita Yee

Quartz | Level 8

## Re: Using Enterprise Guide, Combine Rows in results

Learned how to run a program to accomplish what I wanted!

data OUTPUT_NEW (drop=cage_reason_cd);
set OUTPUT;
by employee_nbr;
length cage_reason_cd_new \$200;
retain cage_reason_cd_new;
cage_reason_cd_new=ifc(first.employee_nbr,cage_reason_cd,catx(',',cage_reason_cd_new,cage_reason_cd));
if last.employee_nbr then output;
run;

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Discussion stats
• 2 replies
• 576 views
• 0 likes
• 2 in conversation