Split a coloumn into different coloumns

[SZ]

I have one column with data in it. I have an sql query and data underneath then another sql query and the output underneath etc.

I need to split the data from one sql query to another. 

Is there a way to do this?

[ChrisTX]

Can you post sample input and desired output?

[SZ]

I wish to use the word =============== SQL ================ as like a delimiter to split my data.

input sample:

Field_1

=============== SQL ================

<query>

============= RESULT ===============

userid;amount;type

1;10;bet

2;20;win

=============== SQL ================

<query>

============= RESULT ===============

userid;amount;type

20;33;bet

=============== SQL ================

<query>

============= RESULT ===============

userid;amount;type

66;9;bet

Desired output:

Field_1	Field_2	Field_1
=============== SQL ================ <query> ============= RESULT =============== userid;amount;type 1;10;bet 2;20;win	=============== SQL ================ <query> ============= RESULT =============== userid;amount;type 20;33;bet	=============== SQL ================ <query> ============= RESULT =============== userid;amount;type 66;9;bet

[afv2688]

You could add to each query a column, like an id, and after transpose the result using this field as key field.

[SZ]

@AFTV2688 add an id to each coloumn like 1,2,3,4,5 etc? I have a large data so I can not do that cause it's impossible to know which id is the same row to the query

[afv2688]

Use a macro to add to each query a unique id, which is going to be the same value for every row of your query:

Query         Value

1                    1

1                    2

1                    3

1                    4

2                    1

2                    2

2                    3

2                    4

etc

afterwards use the transpose tool using the "Query" as key field.

[estherb47]

Hi @SZ !

Yes. My approach involves a bit of Regex and formula magic. 

The Regex splits your single cell into distinct rows. It looks to parse on = followed by SQL. The first row is junk so we can discard, and then a Record ID tool is used to assign "rows". That info will be used to build the horizontal table in the crosstab.

A Formula tool reinserts the = characters removed by Regex. And a crosstab tool transposes from rows into columns.


Please give it a go and let me know if this works.

Cheers! 
Esther

[jdunkerley79]

I would user MultiRow formula tools to make row and column ids then cross tab

The first multi row formula will create a column counter adding one every time SQL line is found

The second is grouped by the output of the first to create a row id

Finally a Cross Tabs makes the table

Sample attached

[estherb47 · Accepted solution]

EDITED: I see @jdunkerley79  and I took a very similar approach. Either will work if the data is in separate rows in your original sample.

@SZ , are all of the data in one cell, or does your information represent separate rows? My first approach parses if it's all in one cell.

If different rows, then this should work:

[SZ]

@jdunkerley79 it didn't work this is my output.