一列多行值合并成一个值(MS SQL SERVER 2008)

现在有两张表，一张手机型号表(PhoneModel),一张手机制造商表(PhoneCompany)

PhoneModel表结构如下:

PhoneCompany表结构如下:

 

,PhoneModel表通过字段coid与PhoneCompany表的coid相关联.现在的需求是如下图":

新增一列查询models,这列显示一个手机制造商所生产的所有手机型号。因为一个手机制造商会生产很多型号的手机，就需要把多条手机型号的记录合并成一个值.

使用for xml path可以实现类似的需求。

SELECT coid,company,(SELECT model+',' FROM dbo.PhoneModel WHERE coid=pc.coid FOR XML PATH('')) AS  models FROM dbo.PhoneCompany AS pc

于是写出了上述SQL句,查询结果如下:

,

最后一个型号总是有逗号(如下图)

于是改进之，把最后一个逗号去掉

SELECT coid,company,LEFT(models,LEN(models)-1) AS models FROM
(
SELECT coid,company,(SELECT model+',' FROM dbo.PhoneModel WHERE coid=pc.coid FOR XML PATH('')) AS  models FROM dbo.PhoneCompany AS pc
)
AS p

查询结果如下

于是把最后一个逗号都去掉了.

但是一个诡异的问题出现了，通过asp程序读取models这列的值会读取不到值，查询分析器里运行是有值的，急中生智再做一次变换

SELECT coid,company,LEFT(models,LEN(models)-1) AS models FROM
(
SELECT coid,company,convert(VARCHAR(5000),(SELECT model+',' FROM dbo.PhoneModel WHERE coid=pc.coid FOR XML PATH(''))) AS  models FROM dbo.PhoneCompany AS pc
)
AS p