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

Question

我正在使用SQL Server 2008。我有一个表格，其中包含以下数据：

Team    Email              Groups
------- ------------------ ------
|Team1|-|[e-mail]|-|A|
|Team1|-|[e-mail]|-|B|
|Team1|-|[e-mail]|-|C|
|Team2|-|[e-mail]|-|A|
|Team2|-|[e-mail]|-|B|
|Team2|-|[e-mail]|-|C|

我想以以下格式获取数据：

Team    A                  B                  C
------- ------------------ ------------------ ------------------
|Team1|-|[e-mail]|-|[e-mail]|-|[e-mail]|
|Team2|-|[e-mail]|-|[e-mail]|-|[e-mail]|

如何实现这个？

- daveomcd

---

你想要做的是数据透视表，参考http://msdn.microsoft.com/en-us/library/ms177410.aspx。语法有时候需要仔细琢磨。 - Michael J Swart

总是3组吗？每组总是有相同的行数吗？ - gbn

@gbn，是的...总是三组。 - daveomcd

Answer 1

使用PIVOT，您可以执行以下操作：

With SampleData AS 
(
SELECT 'Team1' as Team , '[e-mail]' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , '[e-mail]' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , '[e-mail]' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' 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

产生

Team  A                B                C
----- ---------------- ---------------- ----------------
Team1 [e-mail] [e-mail] [e-mail]
Team2 [e-mail] [e-mail] [e-mail]

查看一个运行中的Data.SE示例

在不支持PIVOT的数据库中，您可以对您的表进行多次连接。尽管您可能仍然想这样做，因为正如GBN所指出的那样，我们没有使用聚合函数。

With SampleData AS 
(
SELECT 'Team1' as Team , '[e-mail]' as email, 'A' as Groups
UNION SELECT 'Team1' as Team , '[e-mail]' as email, 'B' as Groups
UNION SELECT 'Team1' as Team , '[e-mail]' as email, 'C' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' as email, 'A' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' as email, 'B' as Groups
UNION SELECT 'Team2' as Team , '[e-mail]' 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'

查看一个运行中的 Data.SE 示例

- Conrad Frix

---

个人而言，如果不需要聚合，我可能会自连接。 - gbn

@gbn，我理解你的观点并更新了我的答案，但我对SELECT DISTINCT TEAM From SampleData也不是很感兴趣，但这可能只是样本数据的问题。 - Conrad Frix

这个代码是有效的，但是我仍然得到了空值，例如这里是我的结果中的一行示例：Team1 NULL NULL [e-mail] Team1 [e-mail] NULL NULL Team1 NULL [e-mail] NULL我希望得到的结果是：Team1 [e-mail] [e-mail] [e-mail] - daveomcd

@Conrad Frix：如果按照我的评论，“每个组的行数始终相同”可能就不需要了。但还是谢谢，点赞+1。 - gbn