Collapse Multiple Rows

[Anonymous]

I'm try to collapse multiple order lines to a single order line when loading them via a script, it's a bit hard to describe but the following should help clarify:

This is the data I'm trying to load:

Order ID	ITEM
1178	A
1178	C
1178	D
1180	B
1180	E
1195	A
1195	B
1195	C
1195	D
1108	A

And this is how I need to display it:

Order ID	Items Ordered
1178	A C D
1180	B E
1195	A B C D
1108	A

Hope this makes sense, any help would be appreciated!

[CELAMBARASAN · Accepted solution]

You could use concat function

Try like this below

Load

     [Order ID],

     Concat(ItemNo, ',') AS [Combined Item]

Resident Orders

Group By [Order ID];

[Siva_Sankar]

use conca function in load script. find the attachment

[#4]

Hi Kevin,

Try creating a straight table with dimension as Order ID and expression Concat(ITEM,' ').

Regards,

Jemimah

[aveeeeeee7en]

Hi

Use Concat() Function.

See this:

TABLE:
LOAD * INLINE [
Order ID, ITEM
1178, A
1178, C
1178, D
1180, B
1180, E
1195, A
1195, B
1195, C
1195, D
1108, A
];

CONCAT_TABLE:
LOAD
[Order ID],
Concat(ITEM,' ') AS [Items Ordered]

RESIDENT TABLE GROUP BY [Order ID];
DROP TABLE TABLE;

Hope that helps.

Regards

Av7eN

[Anonymous]

Thanks for your help, I thought I needed to use concat function but wasn't sure how the Group By would work.