将两行数据合并为一 [英] Merge data in two row into one

问题描述

可能的重复:
连接行值 T-SQL

我是 SQL Server 的新手，尝试过一些从互联网上推荐的技术，比如使用临时变量、XML 路径、COALESCE 等，但不知何故都不能满足我的要求.

I'm new to SQL Server and had tried few techniques that suggested from internet like using temp variable, XML path, COALESCE and etc but all doesn't meet my requirement somehow.

我使用 Toad for SQL Server 5.5 版来创建 SQL 脚本，我用来查询数据库服务器的帐户只有读取权限.因此我相信不能使用 CREATE VIEW 语句.

I'm using Toad for SQL Server version 5.5 to create SQL script and the account I used to query DB server only got READ access. Hence can't use CREATE VIEW statement I believe.

表名:客户

ServerName  Country  contact
----------  -------  -------------
srv1        SG       srv1_contact1
srv1        SG       srv1_contact2
srv1        SG       srv1_contact3
srv2        HK       srv2_contact1
srv2        HK       srv2_contact2
srv3        JP       srv3_contact1
srv3        JP       srv3_contact2
srv3        JP       srv3_contact3
srv4        KR       srv4_contact1

预期输出:

ServerName  Country  contact
----------  -------  -------------------------------------------
srv1        SG       srv1_contact1; srv1_contact2; srv1_contact3
srv2        HK       srv2_contact1; srv2_contact2
srv3        JP       srv3_contact1; srv3_contact2; srv3_contact3
srv4        KR       srv4_contact1

推荐答案

SELECT ServerName, Country, contact = STUFF((SELECT '; ' 
    + ic.contact FROM dbo.Customer AS ic
  WHERE ic.ServerName = c.ServerName AND ic.Country = c.Country
  FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, '')
FROM dbo.Customer AS c
GROUP BY ServerName, Country
ORDER BY ServerName;

这篇关于将两行数据合并为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！