Oracle 11g - 合并单列重复项以创建垂直摘要

Does anybody know how to remove and merge duplicates based on a single column of a multi-column table to create a 'vertical summary'.

是否有人知道如何根据多列表的单个列删除和合并重复项以创建“垂直摘要”。

I.E) Trying to build Table B from Table A

I.E)尝试从表A构建表B.

Table A:
Person Language
Bob    English
Sarah  French
John   Chinese
Bob    French
Sarah  English
Sarah  Chinese

Table B (RESULT): 
Person English French Chinese 
Bob       Y       Y     (null)
Sarah     Y       Y       Y
John      (null)  (null)  Y

How I was thinking originally of doing it was: Creating Table A from the data and then doing the following:

我最初的想法是:从数据创建表A,然后执行以下操作:

Create table summary as 
Select person, (case when language = 'English' then 'Y') as English, (case when        language = 'French' then 'Y') as French, (case when language = 'Chinese' then 'Y') as  Chinese
From Table A;

Finally doing a select distinct of the summary table. The logic is however wrong, especially because distinct does so across all columns, but I only want distinct people names.

最后做一个选择不同的摘要表。然而,逻辑是错误的,特别是因为distinct在所有列中都是如此,但我只想要不同的人名。

The other option I was thinking of in my head was Creating a table populated with only the distinct names of people and empty columns English, French and Chinese. Then using an update statement to populate them via matching to Table A.

我脑子里想到的另一个选择是创建一个只填充人名和空栏英文,法文和中文的表格。然后使用update语句通过匹配表A来填充它们。

Does anybody know a better way / how I could implement this. I'm still in the earlier stages of learning Oracle (especially regarding looping) and any help would be greatly appreciated.

有没有人知道更好的方式/如何实现这一点。我仍处于学习Oracle的早期阶段(特别是关于循环),我们将非常感谢任何帮助。

Thanks!

3 个解决方案

#1

Oracle has a Decode function.

Oracle具有Decode功能。

But what you're looking for is a pivot

但你正在寻找的是一个支点

WITH pivot_data AS (
    SELECT Person, Language
    FROM   A
)
SELECT *
FROM   pivot_data
PIVOT (
    Count(*)        --<-- pivot_clause
    FOR Language         --<-- pivot_for_clause
    IN  ('English', 'French', 'Chinese')   --<-- pivot_in_clause
);

#2

Try this

Create table summary 
as  
Select 
    person, 
    min(case when language = 'English' then 'Y' end) as English, 
    min(case when language = 'French' then 'Y' end) as French, 
    min(case when language = 'Chinese' then 'Y' end) as  Chinese 
From 
    Table A
group by 
    person

#3

Same idea as Sjuul, using pivot, but this gives you the Y/null values you wanted:

与Sjuul一样,使用pivot,但这会为您提供所需的Y / null值:

select * from (
    select person, language, 'Y' as flag from tablea
)
pivot (max(flag) for language
    in ('English' as english, 'French' as french, 'Chinese' as chinese));

So to create a new table based on that:

所以要创建一个基于它的新表:

create table tableb as
select * from (
    select person, language, 'Y' as flag from tablea
)
pivot (max(flag) for language
    in ('English' as english, 'French' as french, 'Chinese' as chinese));

Table created.

select * from tableb order by person;

PERSON          E F C
--------------- - - -
Bob             Y Y
John                Y
Sarah           Y Y Y

If the data in tablea is going to change, you might be better off making tableb a view so it doesn't go out of step.

如果tablea中的数据会发生变化,那么最好不要将tableb视为一个视图,这样它就不会失败。

#1