Hello,
I am attempting to re-create a pivot table into a spreadsheet based on the results that I pull from 3 separate data queries. Each query would return to results to put together into a pivot table. For example:
Query 1:
7/20/2007   25 30 18 10
7/21/2007   15 8 23 14
Query 2:
7/20/2007   10 11 44 23
7/21/2007   32 28 17 11
Query 3:
7/20/2007   33 22 11 24
7/21/2007   14 24 33 40

The final result should look like:
7/20/2007   25 30 18 10
                   10 11 44 23
                   14 24 33 40

I don't know of a way to create a pivot table in MS-SQL 2000 (the new pivot command is available in 2005). There was nothing in the Spreadsheet::WriteExcel either.

I was wondering if using the selectall_hashref within DBI would be an option and then merge the hashes since the keys would line up. I am not proficient with hashes yet and haven't been able to find any good examples of the selectall_hashref online. In addition, the examples that I did find caused me confusion on how to pull the data back. I could create the hash reference, and found a way to get the keys printed out by using a loop with keys %{$hash_ref}, but did not know how to get the data back. I wasn't sure if merging the various hashes together would work either. I was thinking of putting data into 3 separate arrays and the manipulating them to get the data formatted out that I need.

I know this sounds a little confusing, but I was hoping that there might be something that I am missing in my thought process.

Here is the line that I am using to pull the data:
my $hash_ref = $dbh->selectall_hashref($sql,$key_field,{},@values);

[download]

I can print the keys with the following:
for my $id (keys %{$hash_ref}) {
    print "ID => $id\n"; }

[download]

Thank you in advance!

20070904 Janitored by Corion: Added formatting, as per Writeup Formatting Tips

In reply to Merging Results from 3 different queries by MegaVoice

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.