How to properly use pivot/pivot enterprise

JohnyNeg · Post by **JohnyNeg** » 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

ray.wurlod · Post by **ray.wurlod** » Tue Oct 18, 2011 3:54 pm

Derive MEASURE as B15002A_1_EST,B15002A_2_EST,B15002A_3_EST

JohnyNeg · Post by **JohnyNeg** » Tue Oct 18, 2011 8:27 pm

If I derive MEASURE from those values I will only get the values, what I need is the Column header to be pivoted down alongside the value. From my initial post with the way pivot works by default the proposed ending format would be lacking the last column DIMENSION.

@Ray - Thanks for the idea though, not sure I was as clear as I could have been with my initial post.

chulett · Post by **chulett** » Tue Oct 18, 2011 8:42 pm

You need to add new fields with the column header values in them to pivot along with their associated data fields.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 18, 2011 11:12 pm

Surely it's enough that your other columns are your pivot keys?

JohnyNeg · Post by **JohnyNeg** » Wed Oct 19, 2011 11:50 am

@chulett - That's what I was thinking I would have to do but wasn't sure if there was an automated way to do it. I just thought of something once I stepped away from the computer for a few minutes to clear my head so I will post back tomorrow whether or not it worked.

@ray - The other columns only give uniqueness to the location, my need for bringing down the column headers is that it will add more dimensions. Thus giving additional uniqueness for race, sex, and education attainment level (That is the information embedded in the column headers).

I apologize for my lack of proper terminology, I am new to the world of databases and picking it up as I go.

fmou · Post by **fmou** » Wed Oct 19, 2011 2:12 pm

what I need is the Column header to be pivoted down alongside the value

Have you looked at this?

Pivoting with an description
viewtopic.php?t=142442