Load Editor Aggregating by Key

[pnvenkat]

I have a data set that looks like this stored in big query.

ID          Object

1             A

1             B

1             C

2             A

2             D

I would like to load it so that it looks like this (all IDs are made into primary keys, and objects are concatenated):

ID          Objects

1            A, B, C

2            A, D

I've tried using Group By, Aggregate, String_Agg, and Concat but none of them are working.

[sunny_talwar]

Try this

LOAD ID,
  Concat(Object, ', ') as Object
From....
Group By ID;

[pnvenkat]

I get this error: 

The following error occurred:

No qualified path for file: ***

If I modify your script to be:

[objects]:
Load ID,
Concat([Object], ', ') AS Object
FROM `db.objects`
GROUP BY ID;

It just runs forever

[sunny_talwar]

You did add the source where you are loading from, right?

[pnvenkat]

yes it's a table in big query. I just did not add it to the example

[sunny_talwar]

Lol, how would I know what you are doing wrong if you won't even share all the details :). Please share the logfile or the script?

[pnvenkat]

Here you go!
[objects]:
Load ID,
Concat([Object], ', ') AS Object
FROM `db.objects`
GROUP BY ID;

[sunny_talwar · Accepted solution]

May be try this

[objects]:
Load ID,
Concat([Object], ', ') AS Object
GROUP BY ID;
SELECT ID, Object
FROM `db.objects`;

[pnvenkat]

It worked!! Thanks so much!