Hello Friends, I have a table as below. I need to transpose every 4 Columns into a table . Can you please help me to solve this probelm. Query NameHelenAddressFirestone AvenueCityCavespringStateArkansasNameCharlesAddressTurnbridge DriveCityDallasStateTexas Required Result NameAddressCityStateHelenFirestone AvenueCavespringArkansasCharlesTurnbridge DriveDallasTexas

Transpose every n Rows

Hello Friends,

I have a table as below. I need to transpose every 4 Columns into a table . Can you please help me to solve this probelm.

Query

Name	Helen
Address	Firestone Avenue
City	Cavespring
State	Arkansas
Name	Charles
Address	Turnbridge Drive
City	Dallas
State	Texas

Required Result

Name	Address	City	State
Helen	Firestone Avenue	Cavespring	Arkansas
Charles	Turnbridge Drive	Dallas	Texas

Regex

Transformation

Preparation

Accepted answers

jdunkerley79

Use a record id to add a row number.

Then use a formula tool to adjust this to be blocks of 4:

Floor(RecordId/4)

Then use a cross tab to flip round.

Cross Tab blocks of 4.yxmd

jdunkerley79

This is a multi-row formula creating a RecordID field eqial to the running count of times Field1 = Name.

[Row-1:ReordID] is the value of the RecordID on the row above. If the first row this will be 0.

IIF(...) is the inline IF function (I personally prefer over IF THEN ELSE ENDIF but no real difference).

The IIF function evaluates to a 1 if Field1 is equal to Name otherwise it is 0.

Hope that makes sense!

jdunkerley79

I woudl say the easiest way is to transpose all the records so you have one row per grouping

Then use a filter tool to filter on the Result column. You can then just keep the rows where Result = success

All comments

jdunkerley79

Use a record id to add a row number.

Then use a formula tool to adjust this to be blocks of 4:

Floor(RecordId/4)

Then use a cross tab to flip round.

Cross Tab blocks of 4.yxmd

aysha6029

Hi,

Sometimes i have different row where i cannot apply the Floor command. In the below table, i have 4 rows belonging to one record and next three rows that belongs to different set of record. how to do a crosstab for this scenario

Query

Name	Helen
Address	Firestone Avenue
City	Cavespring
State	Arkansas
Name	Charles
Address	Turnbridge Drive
City	Dallas

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703