--> 测试数据:#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([ID] INT,[CID] INT,[Desc] VARCHAR(4)) INSERT #tb SELECT 1,2,'西瓜' UNION ALL SELECT 2,3,'萝卜' UNION ALL SELECT 3,3,'芹菜' UNION ALL SELECT 4,2,'苹果' UNION ALL SELECT 5,1,'豆腐' UNION ALL SELECT 6,2,'葡萄' --------------开始查询--------------------------SELECT [CID],STUFF((SELECT ','+[Desc] FROM #tb WHERE [CID]=a.[CID] FOR XML PATH('')),1,1,'') FROM #tb AS a GROUP BY [CID] ----------------结果---------------------------- /* CID (无列名) 1 豆腐 2 西瓜,苹果,葡萄 3 萝卜,芹菜 */
select cid,stuff((select ','+[desc] from table where a.cid=cid for xml path('')),1,1,'') from table as a where cid=2 group by cid适用sql2005及以上版本
select stuff((select ','+[desc] from tb where cid=2 for xml path('')),1,1,'')
select b.[cid],left(StuList,len(StuList)-1)as tb from ( SELECT [cid], (SELECT [desc]+',' FROM tb WHERE [cid]=a.[cid] FOR XML PATH('')) AS StuList FROM tb A GROUP BY cid ) B
if OBJECT_ID('test')is not null drop table test go create table test(id int,clid int,[desc] varchar(20)) insert into test select 1, 2, '西瓜' union all select 2, 3, '萝卜' union all select 3, 3, '芹菜' union all select 4, 2, '苹果' union all select 5, 1, '豆腐' union all select 6, 2, '葡萄' declare @val varchar(max) select @val=ISNULL(@val+',','')+[desc] from test where clid=2 select @val
1、在前台处理好,以后查询就快很多。 2、可以用自定义函数。 3、查询语句 --sql2005 SELECT clid,STUFF((SELECT ','+[Desc] FROM ##test WHERE clid=a.clid FOR XML PATH('')),1,1,'') FROM ##test AS a GROUP BY clid --sql2000 Declare @Deli VarChar(1) Set @deli='; ' Declare @result varchar(1000) select @result=isnull(@result+@deli,'')+rtrim([Desc]) from ##test where clid='2' group by [Desc] select @result
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] INT,[CID] INT,[Desc] VARCHAR(4))
INSERT #tb
SELECT 1,2,'西瓜' UNION ALL
SELECT 2,3,'萝卜' UNION ALL
SELECT 3,3,'芹菜' UNION ALL
SELECT 4,2,'苹果' UNION ALL
SELECT 5,1,'豆腐' UNION ALL
SELECT 6,2,'葡萄'
--------------开始查询--------------------------SELECT [CID],STUFF((SELECT ','+[Desc] FROM #tb WHERE [CID]=a.[CID] FOR XML PATH('')),1,1,'')
FROM #tb AS a
GROUP BY [CID]
----------------结果----------------------------
/*
CID (无列名)
1 豆腐
2 西瓜,苹果,葡萄
3 萝卜,芹菜
*/
select cid,stuff((select ','+[desc] from table where a.cid=cid for xml path('')),1,1,'')
from table as a where cid=2
group by cid适用sql2005及以上版本
http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html
select stuff((select ','+[desc] from tb where cid=2 for xml path('')),1,1,'')
SELECT [cid],
(SELECT [desc]+',' FROM tb
WHERE [cid]=a.[cid]
FOR XML PATH('')) AS StuList
FROM tb A
GROUP BY cid
) B
if OBJECT_ID('test')is not null
drop table test
go
create table test(id int,clid int,[desc] varchar(20))
insert into test
select 1, 2, '西瓜' union all
select 2, 3, '萝卜' union all
select 3, 3, '芹菜' union all
select 4, 2, '苹果' union all
select 5, 1, '豆腐' union all
select 6, 2, '葡萄'
declare @val varchar(max)
select @val=ISNULL(@val+',','')+[desc] from test where clid=2
select @val
2、可以用自定义函数。
3、查询语句
--sql2005
SELECT clid,STUFF((SELECT ','+[Desc] FROM ##test WHERE clid=a.clid FOR XML PATH('')),1,1,'')
FROM ##test AS a
GROUP BY clid
--sql2000 Declare @Deli VarChar(1)
Set @deli='; '
Declare @result varchar(1000) select @result=isnull(@result+@deli,'')+rtrim([Desc])
from ##test where clid='2'
group by [Desc]
select @result