Merging multiple rows into single row

1 follower
0
Avatar

Hi,

I have a requirement like merging multiple rows into single row based on a column.

For Example, I have data like below:

ID  TIME    DAY        URL_NAME      VALUE  TIME_SPEND
1    12:15  Monday      HOME         4        30
1    13:15  Tuesday     CUSTOMERS    5        21  
1    15:00  Thursday    PLANTS       8        8    
1    16:21  Friday      MANAGEMENT   1        6
...

I want the output like below:

ID  TIME    DAY         URL_NAME     VALUE    TIME_SPEND  TIME1  DAY1        URL_NAME1      VALUE1  TIME_SPEND1  TIME2    DAY2        URL_NAME2      VALUE2  TIME_SPEND2  TIME3    DAY3        URL_NAME3      VALUE3  TIME_SPEND3
1    12:15  Monday      HOME         4        30          13:15  Tuesday     CUSTOMERS      5       21           15:00    Thursday    PLANTS         8       8            16:21    Friday      MANAGEMENT     1       6

I have tried many ways but did not get a solution. Is there any way to do so.

Kindly suggest.

Thank You in Advance.

Swethha Telu

1 comment

  • Avatar
    Gido

    Hi Swethha,

    Based on your example you may use the following approach. 

    Lets assume your example data is on a Worksheet called InputData.
    Create a new Worksheet called TransposedData and group by ID first.
    Group, concatenate and order by TIME after.

    GROUPBY(#InputData!ID)
    GROUPCONCAT(#InputData!TIME;#InputData!TIME)

    Follow up with all other columns.

    GROUPCONCAT(#InputData!DAY;#InputData!TIME)
    GROUPCONCAT(#InputData!URL_NAME;#InputData!TIME)
    GROUPCONCAT(#InputData!VALUE;#InputData!TIME)
    GROUPCONCAT(#InputData!TIME_SPEND;#InputData!TIME)

    You can use List Functions now to find out how many elements are there in the list or access the elements by index. 

    0
Please sign in to leave a comment.