数据
id1 id2
524092 384084
524093 384080
524094 384056
524094 384074
524094 384076显示
id1 id2
524092 384084
524093 384080
524094 384056,384074,384076
id1 id2
524092 384084
524093 384080
524094 384056
524094 384074
524094 384076显示
id1 id2
524092 384084
524093 384080
524094 384056,384074,384076
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'
gocreate 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
goselect id1,dbo.f_hb(id1) from tb group by id1--结果
id1
---------- -----
24092 384084
24093 384080
24094 384056,384074,384076(所影响的行数为 3 行)
-- 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
insert T select '524092', '384084'
union all select '524093', '384080'
union all select '524094', '384056'
union all select '524094', '384074'
union all select '524094', '384076'select * into #T from T order by id1, id2declare @id1 varchar(20), @id2 varchar(200)
update #T set
@id2=case when @id1=id1 then @id2+','+id2 else id2 end,
@id1=id1,
id2=@id2select id1, max(id2) as id2 from #T group by id1--result
id1 id2
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
524092 384084
524093 384080
524094 384056,384074,384076(3 row(s) affected)
drop table #T, T
能不能不用函数,查询可以吗
--
不用函數, 就用臨時表
比如create function a(@traceid int,@quarry_col varchar,@tb varchar,@result_col varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
declare @tablename varchar(200)
set @tablename=@tb
set @str = ''
select @str = @str + '.' + cast(@result_col as varchar) from @tablename(nolock) where @quarry_col = @traceid
set @str = stuff(@str,1,1,'')
return(@str)
end
郁闷中,这周没干什么事,CSDN上花了不少时间
------------------
问题是学习SQL对工作基本上没有什么帮助,被老板知道了天天CSDN可要打屁股了,:)
----------------
跳个有帮助的工作,找个支持上csdn老板
SQL可辅助一下,不喜欢用他来做全职
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s = ''
select @s = @s + ',' + cast( id2 as varchar(8000))
from t14
where id1 = @id1
select @s = stuff(@s , 1 , 1, '')
return @s
endcreate table t14
(
id1 int ,
id2 int
)insert into t14
select 524092 ,384084 union
select 524093 ,384080 union
select 524094 ,384056 union
select 524094 ,384074 union
select 524094 ,384076select id1 ,
dbo.f_unit1(id1)
from t14
group by id1