将重复的值移到唯一列中 [英] Move duplicated values into unique columns

问题描述

我在这里发现了类似的问题:将重复值移动到另一列 ，但我不确定这是否对我有用.

I found a similar problem here: Move duplicate values to another column, but I'm not sure that is going to work for me.

以下是数据的设置方式:

Here's how the data is set up:

Account_ID  Phone Number  Phone Number ID
1           1             1
1           2             2
1           3             3
1           4             4
1           5             5
1           6             6
2           1             1
2           2             2
2           3             3
2           4             4
2           5             5
2           6             6

每个帐户ID在6个电话号码中都有一个条目.我希望它看起来像这样:

Each Account ID has one entry for each of the 6 phone numbers. I want it to look like this:

Account_ID  Phone Number 1  Phone Number 2  Phone Number 3  etc.
1           1               2               3
2           2               2               2

我尝试使用CASE语句，如下所示:

I've tried using a CASE statement like this:

SELECT
Account_ID,
CASE Phone Number ID
WHEN 1 THEN Phone Number END AS "Phone Number 1"
CASE Phone Number ID
WHEN 2 THEN Phone Number END AS "Phone Number 1"
etc.…
GROUP BY 
Case CASE Phone Number ID
WHEN 1 THEN Phone Number END
etc.…

但是对于每个Account_ID，它仍然无法正确地将数据合并到一行中.它将与电话号码ID对应的电话号码放在正确的列中，但是每个Account_ID仍然是其自己的行.

But it still doesn't properly consolidate the data to a single row for each Account_ID. It put the Phone Number that corresponds with the Phone Number ID in the correct column, but each Account_ID is still it's own row.

有什么想法吗?我在上面提供的链接对于这么多字段来说过于嵌套，缓慢且笨拙.无论如何，我写了一个版本来对其进行测试，但是已经运行了15分钟.

Any thoughts? The link I supplied above is way too nested, slow, and clunky for this many fields. I wrote a version of it anyway to test it out, but it's been running for going on 15 minutes.

提前谢谢！

推荐答案

您可以使用 PIVOT 获得所需的输出.

You can use PIVOT to get the output you need.

select * from 
(table1 
   pivot (max("Phone Number") for "Phone Number ID"
  in ('1' as "Phone Number 1",
      '2' as "Phone Number 2",
      '3' as "Phone Number 3",
      '4' as "Phone Number 4",
      '5' as "Phone Number 5",
      '6' as "Phone Number 6"))
 )

SQL FIDE DEMO

SQL FIDDLE DEMO

这篇关于将重复的值移到唯一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！