我有一个表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