当前位置: 代码迷 >> Sql Server >> 不用游标,怎么最高效的完成这类查询
  详细解决方案

不用游标,怎么最高效的完成这类查询

热度:81   发布时间:2016-04-27 17:00:34.0
不用游标,如何最高效的完成这类查询?
我有一个表A,里面有两列id和des,现有值如下:
id       des
1           a
1           b
2           a
2           b

如何最简单的得到这样一个表B:
id       des
1           a,b
2           a,b


------解决方案--------------------

------解决方案--------------------
create table TT(id int, des varchar(10))
insert TT select 1, 'a '
union all select 1, 'b '
union all select 2, 'c '
union all select 2, 'd '

create function column_name(@id int)
returns varchar(1000)
as
begin
declare @c1 varchar(4000)

set @c1 = ' '
select @[email protected]+ ', '+des from tt where id = @id
SELECT @c1=STUFF(@c1, 1, 1, ' ')
return @c1
end

select id , dbo.column_name(id) des from (select distinct id from tt) a
------解决方案--------------------
create table A(id int, des char(1))
insert A select 1, 'a '
union all select 1, 'b '
union all select 2, 'a '
union all select 2, 'b '


select id, des=cast(des as varchar(100)) into #A from A order by id, des

declare @id int, @des varchar(100)
update #A set @des=case when [email protected] then @des+ ', '+des else des end,
@id=id,
[email protected]


select id, max(des) as des from #A group by id

--result
id des
----------- ----------------------------------------------------------------
1 a,b
2 a,b

(2 row(s) affected)
------解决方案--------------------
create table a
([id] int ,des nvarchar(40))
go

insert into a select 1, 'a '
union all
select 1, 'b '
union all
select 2, 'a '
union all
select 2, 'b '


create function atemp(@id int)
returns varchar(100)
as
begin
declare @c1 varchar(40)
set @c1= ' '
select @[email protected]+ ', '+des from a where [id][email protected]
SELECT @c1=STUFF(@c1, 1, 1, ' ')
return @c1
end

select dbo.atemp([id]) from (Select distinct id from a) b
  相关解决方案