Pivoting with an extra field

fmou · Post by **fmou** » Fri Sep 30, 2011 4:17 pm

Hi,

This example explains what I want to pivot:

        Table 1. Simple pivot operation - input data
+-----------------------------------------------------------+
|   REPID   | last_name | Jan_sales | Feb_sales | Mar_sales |
|-----------+-----------+-----------+-----------+-----------|
| 100       | Smith     | 1234.08   | 1456.80   | 1578.00   |
|-----------+-----------+-----------+-----------+-----------|
| 101       | Yamada    | 1245.20   | 1765.00   | 1934.22   |
+-----------------------------------------------------------+
      Table 2. Simple pivot operation - output data
+--------------------------------------------------------+
|    REPID    |  last_name  | Pivot_desc   |   Q1sales   |
|-------------+-------------+--------------+-------------|
| 100         | Smith       | Jan_sales    | 1234.08     |
|-------------+-------------+--------------+-------------|
| 100         | Smith       | Feb_sales    | 1456.80     |
|-------------+-------------+--------------+-------------|
| 100         | Smith       | Mar_sales    | 1578.00     |
|-------------+-------------+--------------+-------------|
 . . .

How can I do that?

Thanks

chulett · Post by **chulett** » Fri Sep 30, 2011 4:24 pm

Add in three "Pivot_desc" fields to the link and hard-code them to your values, pivot those along with the sales columns.

fmou · Post by **fmou** » Fri Sep 30, 2011 9:16 pm

thanks! Didn't know how 2 pivoted columns correlate with each other.

Is it possible, for the 2 pivoted columns to pivot on different number of fields?

Thanks

chulett · Post by **chulett** » Fri Sep 30, 2011 9:33 pm

fmou · Post by **fmou** » Fri Sep 30, 2011 9:34 pm

Didn't know how 2 pivoted columns correlate with each other.

Hold on -- I replied before I try it, BUT it turns out that I was right. There is no correlation between the 2 pivoted columns, at least my results appears to me so.

What I am missing?

Thanks

chulett · Post by **chulett** » Fri Sep 30, 2011 10:07 pm

Sorry but I have no idea what you are trying to say here. If you've tried something and not gotten the results you needed, explain to us what you tried and what you ended up with.

fmou · Post by **fmou** » Sat Oct 01, 2011 8:37 am

Sorry, I was wrong -- working at midnight is not good for thinking.

Thanks

chulett · Post by **chulett** » Sat Oct 01, 2011 9:00 am

Sometimes a fresh brain works wonders. And no, that's not a Young Frankenstein quote.

dsedi · Post by **dsedi** » Wed Aug 15, 2012 10:46 pm

Pivot/Pivot enterprise stage noob here

Post was helpful..Just want to say thanks to OP and Craig...

chulett · Post by **chulett** » Thu Aug 16, 2012 6:55 am

Awesome. :D