Assigning unique ID and creating loop using that ID

[bhargavikn]

Hello,

I'm trying to load first 3 values for each Product ID and assigning that 3 values to 3 different  new columns as below.

Could you please help me to create a unique ID for Product ID and load first 3 values of each product ID.

We have table as below:

Product ID	Country	Product
1001	IND	P1
1001	UAE	P2
1001	USA	P2
1001	AUS	P3
1002	NZ	P1
1002	IND	P4
1003	AF	P5
1003	AL	P6
1003	KH	P3
1004	KY	P7
1004	CM	P8
1004	CAN	P9
1004	BOL	P4

Output should be like below:

UID	Product ID	Country	Product	LID_1	L1	LID_2	L2	LID_3	L3
1	1001	IND	P1	IND	P1	UAE	P2	USA	P2
1	1001	UAE	P2	IND	P1	UAE	P2	USA	P2
1	1001	USA	P2	IND	P1	UAE	P2	USA	P2
1	1001	AUS	P3	Null	Null	Null	Null	Null	Null
2	1002	NZ	P1	NZ	P1	IND	P4	Null	Null
2	1002	IND	P4	NZ	P1	IND	P4	Null	Null
3	1003	AF	P5	AF	P5	AL	P6	KH	P3
3	1003	AL	P6	AF	P5	AL	P6	KH	P3
3	1003	KH	P3	AF	P5	AL	P6	KH	P3
4	1004	KY	P7	KY	P7	CM	P8	CAN	P9
4	1004	CM	P8	KY	P7	CM	P8	CAN	P9
4	1004	CAN	P9	KY	P7	CM	P8	CAN	P9
4	1004	BOL	P4	Null	Null	Null	Null	Null	Null

I hope requirement is clear. please help me with solution.

Thanks.

[MarcoWedel]

what do you mean by "new columns"?

Actual columns in a front end object or fields in a data model table?

[bhargavikn]

Thanks for reply.. need fields in data model