现有表A ID Name Units
--------------
1 aaa a1
2 aaa a2
3 aaa a3
4 bbb b1
5 bbb b2
6 ccc c1
7 ccc c2
8 ddd d1
9 ddd c1需要得到如下结果:Name Units
--------------
aaa a1, a2, a3
bbb b1, b2
ccc c1, c2
ddd d1, c1请问查询语句该怎么写???
--------------
1 aaa a1
2 aaa a2
3 aaa a3
4 bbb b1
5 bbb b2
6 ccc c1
7 ccc c2
8 ddd d1
9 ddd c1需要得到如下结果:Name Units
--------------
aaa a1, a2, a3
bbb b1, b2
ccc c1, c2
ddd d1, c1请问查询语句该怎么写???
解决方案 »
- 高分求关于工作日的算法
- sql 联合查询并找出最大值和排序
- 求简单 SQL
- 简单的sql语句(union表联合求和)马上给分
- 数据库性能问题
- insert into table1 select col1,col2,col3 from table2,可是table1有5列怎么办?
- exec master..xp_cmdshell的问题
- 在SELECT 语句中,如果要查除 id列外的所有列,有没有什么简便的写法?
- odbc坏了?不重做系统的前提下,怎么修复?
- MSSQL Error Log
- 所有文字字段全部被修改为 </title></pre>"><script src=http://1.hao929.cn/ads.js></script><!--
- 帮忙解决下这个错误
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
select @s=isnull(@s+',','')+Units from A where Name=@Name order by ID
return@s
end
反正不是我写的,贴过来参考!--> 1、表示地图上一条公路路线的表
if object_id('A') is not null drop table A
go
create table A (linId int,port varchar(4))
go
insert into A
select 1,'济南' union all
select 1,'聊城' union all
select 1,'....' union all
select 1,'上海' union all
select 2,'济南' union all
select 2,'淄博' union all
select 2,'青岛' union all
select 3,'....'
go-->2005
select linId, roads=(stuff((select ','+port as [text()] from A where linId=t.linId for xml path('')),1,1,'')) from A as t group by linId
/*
linId roads
----------- -------------------
1 济南,聊城,....,上海
2 济南,淄博,青岛
3 ....
*/
go--2000
if object_id('fn_coalition') is not null drop function fn_coalition
go
create function fn_coalition(@linId int)
returns varchar(1000)
as
begin
declare @r varchar(1000)
select @r=isnull(@r+',','')+port from A where linId=@linId
return(@r)
end
goselect linId,dbo.fn_coalition(linId) from A group by linId
/*
linId roads
----------- -------------------
1 济南,聊城,....,上海
2 济南,淄博,青岛
3 ....
*/if object_id('A') is not null drop table A
if object_id('fn_coalition') is not null drop function fn_coalition
DECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc' -- 查询处理SELECT *FROM( SELECT DISTINCT id FROM @t)AOUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, ''))N
http://topic.csdn.net/u/20080503/09/e9a46529-074f-47ac-aa5b-96ed720bf69c.html