日期:2014-05-18  浏览次数:20581 次

如何写?
数据
id1               id2
524092 384084
524093 384080
524094 384056
524094 384074
524094 384076

显示
id1               id2
524092       384084
524093       384080
524094       384056,384074,384076


------解决方案--------------------
create table tb(id1 varchar(10), id2 varchar(10))
insert tb select '24092 ', '384084 '
union all select '24093 ', '384080 '
union all select '24094 ', '384056 '
union all select '24094 ', '384074 '
union all select '24094 ', '384076 '
go

create function f_hb(@a varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(id2 as varchar) from tb where id1 = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

select id1,dbo.f_hb(id1) from tb group by id1

--结果
id1
---------- -----
24092 384084
24093 384080
24094 384056,384074,384076

(所影响的行数为 3 行)

------解决方案--------------------
-- Step 1 建立测试数据=======================================================
-- Create table a_t(id1 int,id2 int)
--
--
-- insert a_t
-- select 524092,384084
-- union all
-- select 524093,384080
-- union all
-- select 524094,384056
-- union all
-- select 524094,384074
-- union all
-- select 524094,384076
--Step 2 建立自定义函数=========================================================
--
-- ALTER FUNCTION dbo.f_str1(@id1 int)
-- RETURNS varchar(100)
-- AS
-- BEGIN
-- DECLARE @re varchar(100)
-- SET @re= ' '
-- SELECT @re=@re+ ', '+ convert(varchar(10),id2)
-- FROM a_t
-- WHERE id1= @id1
-- RETURN(STUFF(@re,1,1, ' '))
-- END

-- Step 3 运行语句=====================================
select [id1],dbo.f_str1(id1) from a_t
group by [id1]


524092 384084
524093 384080
524094 384056,384074,384076

------解决方案--------------------
--试试
create table #a(a int,b varchar(8000))
insert #a
select 524092, '384084 ' union all
select 524093, '384080 ' union all
select 524094, '384056 ' union all
select 524094, '384074 ' union all
select 524094, '384076 '

declare @a int ,@b varchar(8000)

update #a
set @b = case when a = @a then @b + ', '+ b else b end,
@a = a,b = @b
select a,max(b) from #a group by a
drop table #a


a
----------- -----------
524092 384084
524093 384080
524094 384056,384074,384076
------解决方案--------------------
create table T(id1 varchar(20), id2 varchar(200))