THE BORE

General => The Superdeep Borehole => Topic started by: Tieno on December 17, 2010, 12:55:15 PM

Title: Transpose multiple rows to columns
Post by: Tieno on December 17, 2010, 12:55:15 PM

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.

Title: Re: Transpose multiple rows to columns
Post by: demi on December 17, 2010, 01:19:18 PM

Access, now theres a blast from the past

Title: Re: Transpose multiple rows to columns
Post by: Tieno on December 17, 2010, 03:55:09 PM

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

Title: Re: Transpose multiple rows to columns
Post by: pilonv1 on December 17, 2010, 09:35:18 PM

How'd you do it?

Title: Re: Transpose multiple rows to columns
Post by: Tieno on January 01, 2011, 03:28:53 PM

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.

Title: Re: Transpose multiple rows to columns
Post by: drew on January 01, 2011, 03:59:55 PM

fucking fascinating