Rows to Columns

[dlesny]

Hi Sorry if this is already answered somwhere but I cannot find it. I need to split one row into columns:

RecordID	Value
22	5
23	tests
28	0
29	failures
34	0
35	ignored
40	1.003s
41	duration
50	100%
51	successful

 into this:

tests	failures	ignored	duration	successful
5	0	0	1.003s	100%

[mbarone]

Since both your headers and values are in the same column, and can be a mix between numbers and text, I see no way of doing this UNLESS you have a hard and fast "rule" to determine which cells in that column are headers and which are the values that go under the headers.

Is there any such rule you?  In your example, it looks like the odd-numbered record IDs are the headers and the even-numbered record IDs are the values.  Is that always going to be the case?  It also appears that every other row is a header/value (i.e., they alternate)?  Is that always the case?  Will the VERY FIRST row ALWAYS be a value and then alternate from there on out as header/value, header/value?

If there is a rule, then it can be done.

Or are you doing the opposite?  I can't tell from the phrasing of the question versus the examples  you gave.

[dlesny]

Hi, thank you for your help

yes that will always be the case for both, I am transforming HTML cucumber report, the id's are just leftovers from my previous transformations, can be removed or input again from one.

[BenMoss · Accepted solution]

Here is a solution but of course I have made several assumptions.

My guess is there is likely multiple instances which perhaps have a seperate ID column to indicate which instance they belong. In my example I have created two examples. One purely with the data you have and one with how to deal with it if you have multiple instances.

Ben

[mbarone]

What will be the case for both?  That the first record will always be the value and then alternate between header and value?  So you want to transform the 2-column 10 record table INTO the 1 record 5 column table?

[mbarone · Accepted solution]

Here's something based on the assumptions that (1) first record will always be the value, and each record there after will alternate between header and value, and (2) you want to go from 2 columns to however-many-header columns.

Step 1:  Your incoming records

Step 2:  Issue a record ID

Step 3:  Make even records headers, and odd records values:

Step 4:

Assign header-value pairs the SAME number. Doesn't matter what the numbers are, as long as the alternating headers/values have the same number:

Step 5:  First of a double cross-tab.  Get the header/values lined up correctly:

Step 6:  Second of the double cross tab:  Stretch out the data into one row:

That should do it.

[dlesny]

Great, thank you that works perfectly.

[Anusha9]

Hi,

I have been working on the same example in the given workflow you have created with multiple instances in single column what if we have multiple columns like

Input Date:

Field 1	Field 2	Field 3
Name	Name	Name
Mary Guidry	Herman Hesse	Terrance Delpit
Department	Department	Department
Training	Customer Support	HR

And I need the output like:

Name	Department
Mary Guidry	Training
Herman Hesse	Customer Operations
Terrance Delpit	HR

Thanks in advance

[patel_bm]

@Anusha9, all you need is to move all the columns Field 1 ,2 and 3 into a single column before using the solutions already provided by @BenMoss and @mbarone .

Here's my solution workflow for you.

Hope this helps.