如何将多行数据合并为一行?

时间：2021-08-08 05:49:05

I’m using SQL Server 2008. I have data as in this table:

我正在使用SQL Server 2008。我有如下表所示的数据:

Team    Email              Groups
------- ------------------ ------
|Team1|-|email0@email.com|-|A|
|Team1|-|email1@email.com|-|B|
|Team1|-|email2@email.com|-|C|
|Team2|-|email3@email.com|-|A|
|Team2|-|email4@email.com|-|B|
|Team2|-|email5@email.com|-|C|

I want to get the data in this format:

我想以这种格式获取数据:

Team    A                  B                  C
------- ------------------ ------------------ ------------------
|Team1|-|email0@email.com|-|email1@email.com|-|email2@email.com|
|Team2|-|email3@email.com|-|email4@email.com|-|email5@email.com|

How can I achieve this?

我如何做到这一点?

1 个解决方案

#1

7  

Using PIVOT You can do the following

使用PIVOT可以执行以下操作

With SampleData AS 
(
SELECT 'Team1' as Team , 'email0@email.com' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , 'email1@email.com' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , 'email2@email.com' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , 'email3@email.com' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , 'email4@email.com' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , 'email5@email.com' as email, 'C' as Groups
)
SELECT Team, A, B,C FROM 
(SELECT * FROM SampleData) source
PIVOT
(MAX(email) FOR Groups IN ([A], [B], [C]) )as pvt

Produces

生产

Team  A                B                C
----- ---------------- ---------------- ----------------
Team1 email0@email.com email1@email.com email2@email.com
Team2 email3@email.com email4@email.com email5@email.com

See a working Data.SE example

看到一个工作数据。SE的例子

In a DB that doesn't support PIVOT you can instead do multiple joins to your table. Although you may want to anyway, since as GBN pointed out, since we're not using an aggregate.

在不支持PIVOT的DB中，可以对表执行多个连接。尽管你可能想要，因为正如GBN指出的，因为我们不使用聚合。

With SampleData AS 
(
SELECT 'Team1' as Team , 'email0@email.com' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , 'email1@email.com' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , 'email2@email.com' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , 'email3@email.com' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , 'email4@email.com' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , 'email5@email.com' as email, 'C' as Groups
)

SELECT 
    source.Team,
    A.email,
    B.email,
    C.email
FROM 
    (SELECT DISTINCT TEAM From SampleData) source
    LEFT JOIN SampleData A 
    ON source.Team = A.Team
     AND A.GROUPS = 'A'
    LEFT JOIN SampleData B 
    ON source.Team = B.Team
    AND B.GROUPS = 'B'
    LEFT JOIN SampleData C 
    ON source.Team = C.Team
    AND C.GROUPS = 'C'

See a working Data.SE example

看到一个工作数据。SE的例子

#1

7  

Using PIVOT You can do the following

使用PIVOT可以执行以下操作

With SampleData AS 
(
SELECT 'Team1' as Team , 'email0@email.com' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , 'email1@email.com' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , 'email2@email.com' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , 'email3@email.com' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , 'email4@email.com' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , 'email5@email.com' as email, 'C' as Groups
)
SELECT Team, A, B,C FROM 
(SELECT * FROM SampleData) source
PIVOT
(MAX(email) FOR Groups IN ([A], [B], [C]) )as pvt

Produces

生产

Team  A                B                C
----- ---------------- ---------------- ----------------
Team1 email0@email.com email1@email.com email2@email.com
Team2 email3@email.com email4@email.com email5@email.com

See a working Data.SE example

看到一个工作数据。SE的例子

In a DB that doesn't support PIVOT you can instead do multiple joins to your table. Although you may want to anyway, since as GBN pointed out, since we're not using an aggregate.

在不支持PIVOT的DB中，可以对表执行多个连接。尽管你可能想要，因为正如GBN指出的，因为我们不使用聚合。

With SampleData AS 
(
SELECT 'Team1' as Team , 'email0@email.com' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , 'email1@email.com' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , 'email2@email.com' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , 'email3@email.com' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , 'email4@email.com' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , 'email5@email.com' as email, 'C' as Groups
)

SELECT 
    source.Team,
    A.email,
    B.email,
    C.email
FROM 
    (SELECT DISTINCT TEAM From SampleData) source
    LEFT JOIN SampleData A 
    ON source.Team = A.Team
     AND A.GROUPS = 'A'
    LEFT JOIN SampleData B 
    ON source.Team = B.Team
    AND B.GROUPS = 'B'
    LEFT JOIN SampleData C 
    ON source.Team = C.Team
    AND C.GROUPS = 'C'

See a working Data.SE example

看到一个工作数据。SE的例子

标签：

相关文章