How do I merge data into single row?

user08964

I'm working on creating a card that feeds from our data set of activities. Each activity has a status (1,2,3,4,5) and is tied to an individual client.

 

The table produces a result similiar to this:

 

	Activity 1	Activity 2	Activity 3	Activity 4	Activity 5
Client A	Completed
Client A		Completed
Client A			Not Completed
Client A				Not Completed
Client A					Not Completed
Client B	Completed
Client B		Completed
Client B			Completed
Client B				Completed
Client B					Completed

 

How can I merge the information so all client activity results are populated on one line? Would this require a beastmode or is there another option that I am missing?

 

Thanks,

guitarhero23

How is the data organized? What does the same output you show there look like in it's raw form?

 

Is each activity actually it's own column?

user08964

each activity is individually linked to the client name. So each activity produces its own row, which is what is causing the issue.

ST_Superman

I have done this in ETL for you:

The first step is to Group your data - 

 

 

This will give you the following table - 

 

 

Then you will need to only select the "complete / not complete" part.  As a side note, I am not very good at regex but I have written a regex that got the job done: ([^Not Completed|Completed]*)([Not Completed|Completed])([^Not Completed|Completed]*)Now you have the following table: