sql 多行合并单列

时间:2021-11-29 00:56:44

直接贴代码

 

 

sql 多行合并单列sql 多行合并单列View Code
DECLARE @temp TABLE (Id INT, Value CHAR (5))
INSERT INTO @temp
SELECT '1'
    , '09:01'
UNION
SELECT '1'
    , '12:05'
UNION
SELECT '2'
    , '08:04'
UNION
SELECT '2'
    , '17:09'
UNION
SELECT '3'
    , '99888'
    
SELECT *
FROM @temp

SELECT id
    , stuff ((SELECT ',' + rtrim (value)
              FROM @temp
              WHERE id = a.id
                  FOR xml path ('')), 1, 1, '') AS [values]
FROM @temp a
GROUP BY a.id

 

使用函数拼错函数 stuff

Stuff(expression1_Str,startIndex,lengthInt,expression2_Str)

函数共有四个参数,其功能是将expression1_Str中

         自startIndex位置起删除lengthInt个字符,

                       然后将expression2插入到expression1_Str中的startIndex位置