求一条查询语句，把某列的所有值，组成一个字符串返回
本帖最后由 lzxue1989 于 2013-03-20 16:58:18 编辑
有一表，表中的数据如下

xh(nvarchar(6)) no(nvarchar(2))
110886 26
080940 26
050124 05
050125 05
050135 05
070278 05
050127 19
050128 19
050129 19
050130 19
050132 19

现在要返回xh 列所有的值，但是返回一个字符串，即希望得到的结果

'110886','080940','050124','050125','050135','070278','050127','050128','050129','050130','050132'

希望用一条 sql 语句实现 sql?所有值?字符串?列
[解决办法]

select distinct a=stuff((select ','''+convert(varchar,xh)+'''' from 表名 for xml path('')),1,1,'')
 from 表名

这样？你的结果和no是不是得有点关系啊？
[解决办法]

----------------------------
-- Author  :DBA_Huangzj()
-- Date    :2013-03-20 17:00:49
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--Jun 17 2011 00:54:03 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([xh] varchar(6),[no] varchar(2))
insert [huang]
select '110886','26' union all
select '080940','26' union all
select '050124','05' union all
select '050125','05' union all
select '050135','05' union all
select '070278','05' union all
select '050127','19' union all
select '050128','19' union all
select '050129','19' union all
select '050130','19' union all 
 
select '050132','19'
--------------开始查询--------------------------

SELECT  DISTINCT
        SUBSTRING(STUFF(( SELECT    ''',''' + [xh] + ''''
                          FROM      [huang] b
                        FOR
                          XML PATH('')
                        ), 1, 1, ''), 2,
                  LEN(STUFF(( SELECT    ''',''' + [xh] + ''''
                              FROM      [huang] b
                            FOR
                              XML PATH('')
                            ), 1, 1, ''))) '[xh]'
FROM    [huang] a

----------------结果----------------------------
/* 
[xh]
----------------------------------------------------------------------------------------------------------------
'110886'','080940'','050124'','050125'','050135'','070278'','050127'','050128'','050129'','050130'','050132'

*/

[解决办法]

select stuff((select ','''+xh+'''' from tb for xml path('')),1,1,'')

[解决办法]
select stuff((select ''','''+xh from tb for xml path('')),1,2,'')+''''
[解决办法]

--构造测试数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([xh] varchar(6),[no] varchar(2))
insert [tb]
select '110886','26' union all
select '080940','26' union all
select '050124','05' union all
select '050125','05' union all
select '050135','05' union all
select '070278','05' union all
select '050127','19' union all
select '050128','19' union all
select '050129','19' union all
select '050130','19' union all
select '050132','19'
go

--sql2005及以上版本
select 
result=stuff((select ','''+xh+'''' from tb for xml path('')),1,1,'')

--sql2000 
 
declare @s varchar(8000)
select @s=isnull(@s+',''','''')+xh+'''' from tb
select @s as result

--结果
/**
result
-------------------------------------------------------------------
'110886','080940','050124','050125','050135','070278','050127','050128','050129','050130','050132'

(1 行受影响)
**/