Transpose multiple rows to columns

[Tieno]

My data in Access is like this

Code: [Select]

1 10178
2 8827
3 9261
9493
9672
4 5433
5 8391
6 8833
9021
9214

Or more accurately.

Code: [Select]

OrgID ID
1 10178
2 8827
3 9261
3 9493
3 9672
4 5433
5 8391
6 8833
6 9021
6 9214
7 3515
8 8392
9 8351

I need to have it like this

Code: [Select]

OrgID ID1 ID2 ID3
1 10178
2 8827
3 9261 9493 9672
4 5433
5 8391
6 8833 9021 9214


and so forth. I'm not getting further than using manual transpose from the excel copy paste menu, but the data is too big for that.

[demi]

Access, now theres a blast from the past

[Tieno]

I'm a genius, found an unorthodox way. Fuck you demi, you cant keep me down.

[pilonv1]

How'd you do it?

[Tieno]

The unorthodox way was generating html documents per org_id with the Access form generator, then scrape them with a web scraper. Was pretty fast, but kinda bloated. These past days I've been teaching myself some SQL and stumbled upon the group_concat function.

Code: [Select]

select orgID, group_concat(ID SEPARATOR ';') as ID
from table_name
group by orgIDwhich gets me two columns, orgID and ID, with the last one containing all the Ids seperated by ;. Then I can just use the text to columns feature in excel to separate them all. Very simple. With all the tech guys here I'm surprised no one recommended it here to me. Very disappointed.

There's probably an SQL way to get it into separate columns, but this one is very simple.

[drew]

fucking fascinating