更正SQL语法？ [英] Correcting the SQL syntax?

问题描述

非常感谢那些回答。在SQL Server中，有没有一种方法可以从table1中获取数据并输出类似table2的数据？

Thanks very much for those responded. Is there a way in SQL server that takes the data from table1 and outputs the data like table2?

谢谢！

表1：

+---------+-----------+----------+------------------+
|  Name   |    DOB    | Agent ID |    Agent Name    |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Smith            |  
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | John             |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Larry            |
+---------+-----------+----------+------------------+

表2：

+---------+-----------+----------+------------------+
|  Name   |    DOB    | Agent ID |    Agent Name    |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Smith,John,Larry |
+---------+-----------+----------+------------------+

推荐答案

对于SQL Server 2005 +，使用STUFF& FOR XML PATH创建一个用逗号分隔的列表：

For SQL Server 2005+, use the STUFF & FOR XML PATH to create a comma separated list:

SELECT DISTINCT
       t.name,
       t.dob,
       t.agentid,
       STUFF(ISNULL(SELECT ', ' + x.agentname
                      FROM TABLE1 x
                     WHERE x.agentid = t.agentid
                  GROUP BY x.agentname
                   FOR XML PATH ('')), ''), 1, 2, '')
  FROM TABLE1 t

这篇关于更正SQL语法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！