Transposing rows to columns

simak29 has asked for the wisdom of the Perl Monks concerning the following question:

Hello, Newbie here. Is there a way that I can transpose DB rows into columns? I am thinking, that I can read the rows into a hashmap and then do some processing there, but am just not sure how to go about this.

For example: If I have the below table:

 id name dept date_joined email date_sent
       1  P1    Test   12/10/14  Y    12/11/14 10:53:45
       1  P1    Test2  1/1/14    Y    1/05/14 05:25:34
       2  P2    Test  3/1/14  N    Null
       2  P2    Test   8/1/14  Y    8/05/14 11:28:28
       3  P2    Test3  10/1/14 N    Null
[download]

Desired Output is: 
   id   name  Test             Test2          Test3       Y_email  N_E
+mail
   1    P1   12/10/14,8/1/14  1/1/14,3/1/14 10/1/14  12/11/14 10:53:45
+,1/05/14 05:25:35,8/05/14 11:28:28  Null
   2    P2  1/1/14,3/1/14       null         10/1/14 Null, Null

Basically, trying to transpose and flatten it to get one row per recor
+d.

Any ideas how I can do this?
[download]

Can anyone suggest how I can go about this in the simplest way possible? The data I am dealing with is huge.

Thanks in advance

Comment on Transposing rows to columns Select or Download Code

Replies are listed 'Best First'.
Re: Transposing rows to columns by NetWallah (Canon) on Dec 18, 2014 at 07:27 UTC
... The data I am dealing with is huge This suggests that a perl-based solution may not work, and a SQL based solution may be more appropriate. Try some of the ideas proposed in Stackoverflow. "You're only given one little spark of madness. You mustn't lose it." - Robin Williams	[reply]
Re^2: Transposing rows to columns by simak29 (Novice) on Dec 18, 2014 at 19:57 UTC
Thank you ..will check that out. I was initially trying it using Pivots on the SQL Server Database, but was getting thrown off at the concatenation. So thought using a data structure might be simpler... No free lunch, right? :)	[reply]
Re: Transposing rows to columns ( pivot table) by Anonymous Monk on Dec 18, 2014 at 09:06 UTC
Its called a pivot table, its a FAQ, use your database, see reformatting tab delimited file, Melt and casting array!, Re: Melt and casting array! (csv pivot)........	[reply]
Re^2: Transposing rows to columns ( pivot table) by simak29 (Novice) on Dec 18, 2014 at 20:04 UTC
Thank you very much for these links. I'll check them out.	[reply]
Re: Transposing rows to columns by poj (Abbot) on Dec 18, 2014 at 09:00 UTC
I'm having difficulty understanding your desired output. Test shows dates `12/10/14` and `8/1/14` for `id 1` whereas in the input data `8/1/14` is for `id 2` How many records are there in the input and is the data sorted in any way ? poj	[reply] [d/l] [select]
Re^2: Transposing rows to columns by simak29 (Novice) on Dec 18, 2014 at 19:59 UTC
Thanks for responding. 8/1/14 is for both Tests 1 and 2. There are about 1M+ records....	[reply]
Re^3: Transposing rows to columns by Laurent_R (Canon) on Dec 18, 2014 at 23:11 UTC
Hmm, one million records is relatively big, but not really something huge by today's criteria (although it depends also on the size of the records, obviously, and on your hardware). And it is likely to fit into a hash. And a hash in memory is very likely to be at least 100 times more efficient than a SQL database. In other words, use a database if you really have to, use hashes (or nested hashes or other composite data structure in memory, whatever) if you can.	[reply]
Re: Transposing rows to columns by Anonymous Monk on Dec 18, 2014 at 07:54 UTC
MySQL has group_concat, Postgres has string_agg... is that what you're looking for?	[reply]
Re^2: Transposing rows to columns by simak29 (Novice) on Dec 18, 2014 at 19:58 UTC
Thank you. I'm using SQL Server DBA. I'll check for similar functions there.	[reply]