How to properly use pivot/pivot enterprise
Posted: Tue Oct 18, 2011 9:33 am
I am in the process of transforming US census data so that it is conforming with my database format.
Starting format:
GEO_ID2 SUMLEVEL GEO_NAME B15002A_1_EST B15002A_2_EST B15002A_3_EST
107000 160 Birmingham city, Alabama 44000 20500 1017
121184 160 Dothan city, Alabama 30326 14155 577
135896 160 Hoover city, Alabama 37016 17453 171
137000 160 Huntsville city, Alabama 81467 39493 1441
Proposed Ending Format:
GEO_ID2 SUMLEVEL GEO_NAME MEASURE DIMENSION
107000 160 Birmingham city, Alabama 44000 B15002A_1_EST
107000 160 Birmingham city, Alabama 20500 B15002A_2_EST
107000 160 Birmingham city, Alabama 1017 B15002A_3_EST
121184 160 Dothan city, Alabama 30326 B15002A_1_EST
121184 160 Dothan city, Alabama 14155 B15002A_2_EST
121184 160 Dothan city, Alabama 577 B15002A_3_EST
135896 160 Hoover city, Alabama 37016 B15002A_1_EST
135896 160 Hoover city, Alabama 17453 B15002A_2_EST
135896 160 Hoover city, Alabama 171 B15002A_3_EST
137000 160 Huntsville city, Alabama 81467 B15002A_1_EST
137000 160 Huntsville city, Alabama 39493 B15002A_2_EST
137000 160 Huntsville city, Alabama 1441 B15002A_3_EST
1. I understand how to use the pivot stage to get the MEASURE column to transpose down the values.
2. What I do not know how to do and is crucial for what I need is how to treat the column headers as data so that i can transpose that as well along with the measures. The reason for needing this DIMENSION column is that it is an ID for how I need to retrieve each measure
Feel free to post any questions or comments if I need to give more information to make this more clear.
Thanks in advance
Starting format:
GEO_ID2 SUMLEVEL GEO_NAME B15002A_1_EST B15002A_2_EST B15002A_3_EST
107000 160 Birmingham city, Alabama 44000 20500 1017
121184 160 Dothan city, Alabama 30326 14155 577
135896 160 Hoover city, Alabama 37016 17453 171
137000 160 Huntsville city, Alabama 81467 39493 1441
Proposed Ending Format:
GEO_ID2 SUMLEVEL GEO_NAME MEASURE DIMENSION
107000 160 Birmingham city, Alabama 44000 B15002A_1_EST
107000 160 Birmingham city, Alabama 20500 B15002A_2_EST
107000 160 Birmingham city, Alabama 1017 B15002A_3_EST
121184 160 Dothan city, Alabama 30326 B15002A_1_EST
121184 160 Dothan city, Alabama 14155 B15002A_2_EST
121184 160 Dothan city, Alabama 577 B15002A_3_EST
135896 160 Hoover city, Alabama 37016 B15002A_1_EST
135896 160 Hoover city, Alabama 17453 B15002A_2_EST
135896 160 Hoover city, Alabama 171 B15002A_3_EST
137000 160 Huntsville city, Alabama 81467 B15002A_1_EST
137000 160 Huntsville city, Alabama 39493 B15002A_2_EST
137000 160 Huntsville city, Alabama 1441 B15002A_3_EST
1. I understand how to use the pivot stage to get the MEASURE column to transpose down the values.
2. What I do not know how to do and is crucial for what I need is how to treat the column headers as data so that i can transpose that as well along with the measures. The reason for needing this DIMENSION column is that it is an ID for how I need to retrieve each measure
Feel free to post any questions or comments if I need to give more information to make this more clear.
Thanks in advance