Merging multiple rows into single row

Follow

Swethha Telu

• May 18, 2017 05:11

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.

0

• 

  Gido

  • May 18, 2017 08:27
  • Edited

  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

  Comment actions Permalink

Please sign in to leave a comment.