Cross Tab data transformation

[Rajat_Rehria]

Hi Community,

I want to Pivot the data like below please help me getting the desired results, i tried using Cross Tab but unable get the results.

I am also getting the below aggregating options which i am not sure why not working as required

Concatenate

First

Last

Input:

Categories	Accounts
H	145
B	125
H	6621
D	112
B	5400
F	5440
D	1223
H	221

Output:

H	B	D	F
145	125	112	5440
6621	5400	1223
221

[JosephSerpis]

Hi @Rajat_Rehria you would need to rank your data based on the Categories in order to keep them as seperate rows. I've mocked up an approach which showcases one way to tackle this.

[apathetichell]

tile tool - unique value - categories.

use tile sequence number as a primary key in your cross tab. use concatenate.

[acotta17]

Hi @Rajat_Rehria,

You will get your desired output by using the two tools mentioned in the steps below: 

1. Use the Tile Tool and select Unique value in the method drop-down while selecting the first column as unique value. 

2. Use the Cross-tab Tool and select the Tile_SequenceNum in the Group by while selecting the first column as headers and second column as values.

(Optional: You can use the Sort Tool on the Tile_SequenceNum column and use the select tool to then deselect the column from the output)

[Screenshot for the two steps and output attached below]

Hope this helps :)

 

 

Output: