Hi all,
I could really use some help solving the following. I have the following (simplified) table:
ID
| LINE | ITEM | AMOUNT | REVENUE |
|---|
| 392847 | 1 | A | 2 | 200 |
| 392847 | 2 | B | 1 | 50 |
| 392847 | 3 | D | 3 | 400 |
| 392848 | 1 | C | 1 | 20 |
| 392848 | 2 | E | 1 | 60 |
| ... | ... | ... | ... | ... |
What I need is a select-statement on the upper table to result in the following:
ID
| A_PRESENT | B_PRESENT | C_PRESENT | D_PRESENT | E_PRESENT | A_AMOUNT | B_AMOUNT | C_AMOUNT | D_AMOUNT | E_AMOUNT | A_REVENUE | B_REVENUE | C_REVENUE | D_REVENUE | E_REVENUE |
|---|
| 392847 | 1 | 1 | 0 | 1 | 0 | 2 | 1 | 0 | 3 | 0 | 200 | 50 | 0 | 400 | 0 |
| 392848 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 20 | 0 | 60 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
So what's happening is that we take every unique ID from the source table and then connect this to a set of columns that tells us more about the items that were included in this unique ID. So first, for all possible ITEMs, we give a binary value for whether the item is present or not. Second and last, we store the AMOUNTs and REVENUEs again for each individual item. *I want the items (A to E) to be derived from the source table and not be predefined. I was trying to make this work using the PIVOT function, but right now I'm not sure that's the right direction. What do you think?
*EDIT: I can find a way to have the set of ITEMs predefined before running the query. It's not necessary to derive the ITEMS from the source table.
Thanks for any help on this, appreciate it!