January 23, 2015 at 7:57 am
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").
For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
UNION
SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns.
Performance of UNION good, but another person here chose to use PIVOT instead.
I can' find any articles on PIVOT vs. UNION for "de-flattening". Anybody here have some solid technical knowledge bearing on this?
January 23, 2015 at 8:13 am
I cannot see how you could use PIVOT to generate the same results as your SELECT ... UNION example.
Would you be so good as to post the solution please?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2015 at 9:15 am
I believe that you don't need PIVOT, you need UNPIVOT. And you might want to use UNION ALL instead of UNION.
There's a third alternative which is explained in here by Dwain Camps: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply