按表table1里的字段cid=1查询表得来的数据,怎么把它链成一个字符串,,用sql 语名或者sql 自定义函数

Question

按表table1里的字段cid=1查询表得来的数据,怎么把它链成一个字符串,,用sql 语名或者sql 自定义函数

Answer 1

declare @s varchar(8000)
select @s = isnull(@s +',','')+name from tb where cid=1
select @s

Answer 2

declare @sql varchar(1000)
select @sql=isnull(@sql+','+'')+col1+col2 from table1 where cid=1
select @sql

Answer 3

select s=stuff((select ','+col from tb for xml path('')),1,1,'')

Answer 4

需求不明确
如果单字段返回多条记录 参考1,2楼
如果多字段返回一条记录用+连接(注意类型转换)
如
select ltrim(ID)+[name]+convert(varchar(10),时间,120)
from tb where id=1

Answer 5

再陈述一下:
有两个表table1  table2
table1的形式
cid  name
1     aaa
2     bbb
3     ccctable2的形试为:
id cid filename
 1  1   eeee
 2  1   ewwww
 3  1   5555
 4  2   wwww
 5  2   wwwww然后我想查询成如下形式的表
cid name filelist
 1   aaa  eeee-ewwww-5555
 2   bbb  wwww-wwwww请问一下怎么实现啊,,

Answer 6

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-16 13:45:47
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:t1IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1]([cid] INT,[name] NVARCHAR(10))
INSERT [t1]
SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ALL
SELECT 3,'ccc'
GO
--SELECT * FROM [t1]--> 生成测试数据表:t2IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2]([id] INT,[cid] INT,[filename] NVARCHAR(10))
INSERT [t2]
SELECT 1,1,'eeee' UNION ALL
SELECT 2,1,'ewwww' UNION ALL
SELECT 3,1,'5555' UNION ALL
SELECT 4,2,'wwww' UNION ALL
SELECT 5,2,'wwwww'
GO
--SELECT * FROM [t2]-->SQL查询如下:select *,
filelist=STUFF((select ','+filename from t2 where cid=t1.cid for XML path('')),1,1,'')
from t1
/*
cid         name       filelist
----------- ---------- --------------------------
1           aaa        eeee,ewwww,5555
2           bbb        wwww,wwwww
3           ccc        NULL(3 行受影响)*/

Answer 7

select *
from t1 
cross apply (select filelist=STUFF((select '-'+filename from t2 where t2.cid=t1.cid for XML path('')),1,1,'')) t2
where filelist is not null
/*
cid         name       filelist
----------- ---------- --------------------------
1           aaa        eeee,ewwww,5555
2           bbb        wwww,wwwww(2 行受影响)*/还得改下

Answer 8

if not object_id('table1') is null
drop table table1
Go
Create table table1([cid] int,[name] nvarchar(3))
Insert table1
select 1,N'aaa' union all
select 2,N'bbb' union all
select 3,N'ccc'
Go
if not object_id('table2') is null
drop table table2
Go
Create table table2([id] int,[cid] int,[filename] nvarchar(5))
Insert table2
select 1,1,N'eeee' union all
select 2,1,N'ewwww' union all
select 3,1,N'5555' union all
select 4,2,N'wwww' union all
select 5,2,N'wwwww'
Go
select a.cid,
       a.name,
       filelist=stuff((select '-'+[filename]
                       from table2 
                       where [cid]=a.[cid] for xml path('')),1,1,'')
from table1 a,table2 b 
where a.[cid]=b.[cid]
group by a.cid,a.name
/*
cid         name filelist
----------- ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           aaa  eeee-ewwww-5555
2           bbb  wwww-wwwww(2 個資料列受到影響)*/

Answer 9

为什么在我电脑上运行,都出现这样的错误码:
错误如下:
===================
服务器: 消息 170，级别 15，状态 1，行 5
第 5 行: 'xml' 附近有语法错误。
====================