Join horizontal data set to vertical dataset

[Sshasnk]

I have two datasets and I have to join them but the challenge is one is vertical and another one is horizontal

Input:

Dataset 1:

Number	samp	Fund	Type	Name	value	Selection type
1		ABC	M	Sell	123	Yes
1		ABC	M	Buy	1456	Yes
	2	ABC		Buy	0	No
	2	ABC		Buy	0	No
	2	ABC		Sell	0	No
	3	ABC		Sell	0	No
	3	ABC		Call	0	No
	3	ABC		Call	0	No
	3	ABC		Call	0	No
	3	ABC		Put	0	No
	3	ABC		Put	0	No
	3	ABC		Put	0	No

Dataset 2:

Fund	Sell	Buy	Call	Put
ABC	123	1456	0	0
ABC	0	0	0	0
ABC	0	0	0	0

The rule is very simple I have to join based on fund, name, and value. 

After that, I have to paste the value of the selection type in front

Output:

Fund	Sell	Buy	Call	Put	Selection type sell	Selection type Buy	Selection type Call	Selection type Put
ABC	123	1456	0	0	Yes	Yes	No	No
ABC	0	0	0	0	No	No	No	No
ABC	0	0	0	0	No	No	No	No

[grazitti_sapna · Accepted solution]

Hi @Sshasnk , here is my solution to your problem.

[DanFlint]

Hi @Sshasnk 

By using transpose and cross tab tools, I have been able to join the 2 datasets together and summarise to the output view that you need.

The dynamic rename tool can also be really helpful when you need to add the same string to a number of field names

Hope this helps!