SQL Server 反透视列

2022-01-22 00:00:00 pivot sql sql-server

我有一个表，我想在 SQL 语句中取消透视.它由一个人和电话 1 到 5 组成.现在我正在为每部电话做一个联合，但我担心它会导致性能问题.

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.

列:

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

推荐答案

我更喜欢 UNPIVOT 但至于你的解决方案 -
确保您使用的是 UNION ALL 而不是 UNION.
UNION ALL 只是将一个查询结果溢出另一个查询结果.
UNION 消除了行重复，这是您为性能付出代价的地方.

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

相关文章