根据值将SQL列拆分为多个列

Question

我有一个像下面这样的表格：

Opp_ID     Role_Name     Role_User_Name
---------------------------------------
1          Lead          Person_one
1          Developer     Person_two
1          Developer     Person_three
1          Owner         Person_four
1          Developer     Person_five

现在，我需要根据值将Role_Name列拆分为3个不同的列。 我需要确保没有NULL值，因此表格应如下所示：

Opp_ID     Lead        Developer     Owner
--------------------------------------------------
1          Person_one  Person_two    Person_four
1          Person_one  Person_three  Person_four
1          Person_one  Person_five   Person_four

我的代码目前是：

SELECT
    ID,
    CASE WHEN Role_Name = 'Lead' THEN Role_User_Name ELSE NULL END AS Lead,
    CASE WHEN Role_Name = 'Developer' THEN Role_User_Name ELSE NULL END AS Developer,
    CASE WHEN Role_Name = 'Owner' THEN Role_User_Name ELSE NULL END AS Owner
FROM 
    [table1]
WHERE 
    Role_Name IN ('Lead','Developer','Owner')

很不幸，这会返回以下结果：

Opp_ID     Lead        Developer     Owner
-------------------------------------------
1          Person_one  NULL          NULL
1          NULL        Person_two    NULL
1          NULL        Person_three  NULL
1          NULL        NULL          Person_four
1          NULL        Person_five   NULL

我认为想要让这个工作起来，您需要将代码重新与自身连接，但我似乎无法让它正常工作。

- user3515329

---

你使用的是哪种数据库管理系统？ - Matt

这是一个“PIVOT”操作，但如何实现取决于您使用的数据库。 - Shawn

嗨，我正在使用SQL Server 2017。 - user3515329

在你的 ELSE NULL 中，为什么不写成 ELSE Role_User_Name 呢？另外，在你最后一个例子中，开发人员的空值应该是什么结果？ - Alexandre Elshobokshy

我错了。你不需要使用 PIVOT，而是需要几个自连接。 - Shawn

Answer 1

你可以切换到聚合模式：

SELECT ID,
       MAX(CASE WHEN Role_Name = 'Lead' THEN Role_User_Name END) AS Lead,
       MAX(CASE WHEN Role_Name = 'Developer' THEN Role_User_Name END) AS Developer,
       MAX(CASE WHEN Role_Name = 'Owner' THEN Role_User_Name END) AS Owner
FROM [table1]
WHERE  Role_Name IN ('Lead', 'Developer', 'Owner')
GROUP BY ID;

如果您需要多个人，您可能希望使用STRING_AGG()。

请注意，我删除了ELSE NULL。这是多余的。没有ELSE从句时，CASE表达式在没有匹配项时返回NULL。

- Gordon Linoff

Answer 2

为了将每个开发者和领导的信息应用于您的所有者的 Opp_ID，您需要使用以下类似代码:

SELECT o.opp_id
    , o.Role_User_Name AS Owner
    , l.Role_User_Name AS Lead
    , d.Role_User_Name AS Developer
FROM t1 AS o
LEFT OUTER JOIN t1 l ON o.opp_id = l.opp_id AND l.Role_Name = 'Lead'
LEFT OUTER JOIN t1 d ON o.opp_id = d.opp_id AND d.Role_Name = 'Developer'
WHERE o.Role_Name = 'Owner'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=da4daea062534245bed474f93ffafbb7

- Shawn

---

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4e264e56a5295e18b4272b112725cf75 <<< 多个所有者和负责人，以及一个没有负责人的 Opp_ID。 - Shawn

Answer 3

你也可以像下面这样在Pivot结果上使用first_value函数

查看演示

select 
    opp_id,
    lead=COALESCE([lead],FIRST_VALUE([Lead]) over( order by opp_id )),
    Developer=COALESCE([Developer],FIRST_VALUE([Developer]) over( order by opp_id )),
    Owner=COALESCE([Owner],FIRST_VALUE([Owner]) over( order by opp_id ))
from 
(select opp_id,Role_Name,
 Role_User_Name,
 rn=row_number() over( partition by Role_Name order by (select 1))
 from
 table1)
 src
pivot
(max(Role_user_name) for role_name in ([Lead],[Developer],[Owner]))p

- DhruvJoshi

Answer 4

我更喜欢使用交叉连接

select o.opp_id,
   o.role_user_name o, 
   l.role_user_name l, 
   d.role_user_name d
from t1 o cross join t1 l cross join t1 d
where o.role_name = 'Owner' 
   and l.role_name = 'Lead' 
   and d.role_name = 'Developer'

在此输入图片描述

- zulmach