如何使用SELECT与UNION按Primary Ind标识实现电话列拆分聚合

Problem Overview #

You've got a table with multiple phone numbers per contact, flagged by a Primary Ind to distinguish primary vs non-primary numbers:

Name  Primary phone  Primary Ind
Manju 11             Y
Manju 22             N
Tyagi 33             N
Tyagi 44             Y

Your goal is to pivot this into a single row per contact, with primary numbers in a Primary Phone column and non-primary in Non Primary, like this:

Name   Primary Phone  Non Primary
Manju  11             22
Tyagi  44             33

And you're restricted to using only SELECT and UNION statements.

Working SQL Solution #

Here's the query that meets your requirements:

SELECT
    Name,
    MAX(CASE WHEN primary_flag = 'Y' THEN phone END) AS "Primary Phone",
    MAX(CASE WHEN primary_flag = 'N' THEN phone END) AS "Non Primary"
FROM (
    -- Pull all entries with their primary flag
    SELECT Name, "Primary phone" AS phone, "Primary Ind" AS primary_flag
    FROM your_source_table
    UNION ALL
    -- Re-pull the same data to ensure we have full coverage for aggregation
    SELECT Name, "Primary phone" AS phone, "Primary Ind" AS primary_flag
    FROM your_source_table
) AS combined_records
GROUP BY Name;

How It Works #

Let's break down the logic step by step:

• Combine Datasets: We use two SELECT statements to fetch the full table data twice, then merge them with UNION ALL (we use ALL to avoid unnecessary duplicate checks, which keeps the query efficient).
• Pivot via Aggregation: By grouping the combined data on Name, we use MAX() paired with CASE statements to filter and extract the correct phone number for each column. Since each contact has exactly one primary and one non-primary number, MAX() will reliably grab the single relevant value for each column in the group.

Note: Replace your_source_table with the actual name of your table. If your SQL dialect doesn't require quotes around column names with spaces, you can omit them.

内容的提问来源于stack exchange，提问作者unnikrishnan