SQL server 多行一列,把这些结果拼接成一个字符串

查询结果为多行一列,现在要把这些结果拼接成一个字符串!

select [values]=stuff((select ‘,’+coal_id from statement t for xml path(”)), 1, 1, ”)

1.for xml path

将查询结果变成xml

a. select well_id,coal_id from cbm_well_coalbed_info t for xml path(‘liuxm’)

产生的结果如下:

<liuxm>

<well_id>8ad882351ede89c6011eecb09b290002</well_id>

<coal_id>8ad882351ec8d47c011ec99e01b6002b</coal_id>

</liuixm>

2. stuff函数

删除指定长度的字符串并在指定的起始点插入另一组字符
STUFF ( character_expression , start , length , character_expression )
character_expression :操作的字符,
start:删除和插入的起始点,
length:删除的长度,
character_expression :要插入的字符

发表评论

电子邮件地址不会被公开。 必填项已用*标注

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>