SQLserver2008一对多,多行数据显示在一行

时间:2021-07-27 23:14:59

现在有一个需求

我们有一张表employee
EmpID EmpName
---------- -------------
张山
张大山
张小山
李菲菲
李晓梅
Result I need in this format: EmpID EmpName
---------- -------------
张山, 张大山, 张小山
李菲菲, 李晓梅

如果使用MySQL,可以很简单的实现

selecet  group_concat(empname)  from  employee  group by empid;

但是最近公司中使用的是SQLserver,没有group_concat的功能

最后在https://*.com/questions/8868604/sql-group-concat-function-in-sql-server找到了答案

select distinct EmpID,
  (select EmpName+‘,’
    from employee t2
    where t2.EmpId = t1.EmpId
    For XML PATH('')
  )Concatenated
from employee t1;

对于这个表是没有问题的,本以为就这样结束了,事实证明还是too young!

实际中又遇到了报错:

[Err] 22018 - [SQL Server]在将 varchar 值 ',' 转换成数据类型 int 时失败。

很快找到原因,其中一个字段的字段类型是int类型,但是在SQL语句中,我们有一个需求是对ID进行拼接,

但是我们使用了字符串(‘,’元凶在此,再加上字段类型本身是int),还是经过一番苦苦查找和分析,找到在SQLserver中有一个字符串函数stuff和convert这两个工具

STUFF字符串函数是将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符,然后将第二个字符串插入到开始位置的第一个字符串中,语法如下。

STUFF(<character_expression>,<start>,<lenth>,<character_expression>)
<character_expression>参数是给定的字符串数据,可以是字符或二进制数据的常量,变量或列。
<start>参数是一个整数值,指定开始删除和插入的位置,可以是BIGINT类型。如果<开始>或<长度>参数为负数,则返回NULL字符串。
  如果<start>参数比第一个<character_expression>长,则返回一个NULL字符串。
<length>参数可以是BIGINT类型,它是一个整数,指定要删除的字符数。
  如果<length>比第一个<character_expression>长,则删除发生到最后一个<character_expression>中的最后一个字符。 convert

定义和用法

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

语法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

只使用convert,就是这样

SELECT DISTINCT userid,(
SELECT convert(varchar,area_id)+','
FROM userinfo_attarea t2
WHERE t2.employee_id=t1.employee_id
FOR XML PATH('') as area_ids 输出:
  userid area_ids
    1   4,5,6,

两个一起用,为了去掉后面的符号,

SELECT DISTINCT userid,STUFF((
SELECT ','+ convert(varchar,area_id)
FROM userinfo_attarea t2
WHERE t2.employee_id=t1.employee_id
FOR XML PATH('')
), , , '') 输出:
  userid area_ids
    1  4,5,6