I have a table that I would like to unpivot in a SQL statement. It consists of a person and phone 1 through 5. Right now I'm doing a union for each phone but I fear it is causing performance issues.
Columns:
PERSON_GUID,
PHONE_1, PHONE_1_VOICE_FLG,
PHONE_2, PHONE_2_VOICE_FLG,
PHONE_3, PHONE_3_VOICE_FLG,
PHONE_4, PHONE_4_VOICE_FLG,
PHONE_5, PHONE_5_VOICE_FLG
How would I best unpivot the row with performance in mind so that the results are:
PERSON_GUID, PHONE_NO, VOICE_FLG
I prefer UNPIVOT
but as for your solution -
Make sure you are using UNION ALL
and not UNION
.
UNION ALL
just spills one query result after the other.
UNION
eliminates rows duplications and this is where you pay in performance.
select PERSON_GUID,PHONE_NO,
case right(col,1)
when 1 then PHONE_1_VOICE_FLG
when 2 then PHONE_2_VOICE_FLG
when 3 then PHONE_3_VOICE_FLG
when 4 then PHONE_4_VOICE_FLG
when 5 then PHONE_5_VOICE_FLG
end VOICE_FLG
from t unpivot (PHONE_NO for col in
(PHONE_1,PHONE_2,PHONE_3,PHONE_4,PHONE_5)) u