表信息
id words rank
1 hello 1
1 everybody 2
2 i 1
2 am 2
2 programmer 3结果:
1 hello everybody
2 i am programmersql语句如何写?
id words rank
1 hello 1
1 everybody 2
2 i 1
2 am 2
2 programmer 3结果:
1 hello everybody
2 i am programmersql语句如何写?
好象我也问过
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)
多个前列的合并
数据的原始状态如下:
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现,变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)if object_id('pubs..tb') is not null
drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '差', 6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '好', 2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p', 'c', '一般', 4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '差', 8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '好', 7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w', 'e', '一般', 1)
goif object_id('pubs..test') is not null
drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)(所影响的行数为 2 行)create table b
(col varchar(20))insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')
declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)
create table tb(id int,words varchar(100),rank int)
insert tb
select 1, 'hello', 1
union all select 1, 'everybody', 2
union all select 2, 'i', 1
union all select 2, 'am', 2
union all select 2, 'programmer', 3
create function c(@id int)
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+words+' ' from tb where id=@id order by rank
return @c
endgo
select distinct id,dbo.c(id) from tb/*结果
1 hello everybody
2 i am programmer
*/
drop table tb
gocreate table tb(id varchar(10),words varchar(20),rank int)
insert into tb(id,words,rank) values('1', 'hello' , 1)
insert into tb(id,words,rank) values('1', 'everybody' , 2)
insert into tb(id,words,rank) values('2', 'i' , 1)
insert into tb(id,words,rank) values('2', 'am' , 2)
insert into tb(id,words,rank) values('2', 'programmer', 3)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ' ' + cast(words as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id ,dbo.f_hb(id) as words from tbdrop table tb/*
id words
---------- ---------------
1 hello everybody
2 i am programmer(所影响的行数为 2 行)*/
insert into t1 values(1,'everybody',2)
insert into t1 values(2,' i',1)
insert into t1 values(2,'am ',2)
insert into t1 values(2,'programmer ',3)select * from t1drop function ff
create function ff(@fid int )
returns varchar(50)as
begin
declare @ret varchar(20)
set @ret='' select @ret=@ret+' ' + word from t1 where id=@fid order by rank
set @ret=stuff(@ret,1,1,'')return @ret
endselect min(id) ,结果=dbo.ff(min(id)) from t1 group by id order by id
1 hello everybody
2 i am programmer
(所影响的行数为 2 行)
create table NewT02(id int, words varchar(50), rank int)
insert into NewT02 values(1, 'hello', 1)
insert into NewT02 values(1, 'everybody', 2)
insert into NewT02 values(2, 'i', 1)
insert into NewT02 values(2, 'am', 2)
insert into NewT02 values(2, 'programmer', 3)declare @id int, @str varchar(50), @words varchar(50)
declare @temp table(id int, str varchar(50))
declare xx cursor
for select distinct id from NewT02
open xx
fetch xx into @id
while @@fetch_status=0
begin
select @str='', @words=''
declare yy cursor
for select words from NewT02 where id=@id order by rank
open yy
fetch yy into @words
while @@fetch_status=0
begin
select @str=@str+' '+@words
fetch yy into @words
end
close yy
deallocate yy
insert into @temp(id, str)
values(@id, @str)
fetch xx into @id
end
close xx
deallocate xx
select * from @temp
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+words+' ' from one where id=@id order by rank
return @c
end
这是一种方法,而另一种方法是利用游标。如同楼上select distinct id ,dbo.c(id) from one
declare @sn smallint,@sn1 smallint,@groups varchar(50),@groups1 varchar(50)create table #aa
(
id smallint,
words varchar(20)
)
open ee
fetch from ee into @sn,@groups
set @sn1=@sn
set @groups1=''
while (@@fetch_status=0)
begin
if (@sn=@sn1)
begin
set @groups1=@groups1+space(2)+ @groups+ space(2)
endelse
begin
insert into #aa (id,words) values (@sn1,@groups1)
set @sn1=@sn
set @groups1=@groups
endfetch from ee into @sn,@groups
end
insert into #aa (id,words) values (@sn1,@groups1) --础aaい程兵snの癸莱才﹃ぇ㎝
select * from #aa
drop table #aa
close eedeallocate ee
drop table aaid words
------ --------------------
1 hello everybody
2 i am programmer (所影响的行数为 2 行)
create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1, 'hello', 1)
insert into t1 values(1, 'everybody' , 2)
insert into t1 values(2, 'i' ,1)
insert into t1 values(2, 'am' ,2)
insert into t1 values(2, 'programmer' ,3)create function tt(@id int)
returns varchar(50)
as
begin
declare @s varchar(50)
set @s=''
select @s=@s+' '+words from t1
where id=@id
order by rank
set @s=right(@s,len(@s))
return(@s)
endselect id,dbo.tt(id) as words from t1
group by idid words
----------- --------------------------------------------------
1 hello everybody
2 i am programmer(所影响的行数为 2 行)drop table T1
drop function tt
create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1, 'hello', 1)
insert into t1 values(1, 'everybody' , 2)
insert into t1 values(2, 'i' ,1)
insert into t1 values(2, 'am' ,2)
insert into t1 values(2, 'programmer' ,3)declare @s varchar(100)
declare @id int
declare @tb table( id int,words varchar(100))declare roy cursor for select id, words from t1
order by rankopen roy
fetch next from roy into @id, @s
while @@fetch_status=0
begin
if not exists(select 1 from @tb where id=@id)
begin
insert into @tb(id,words) select @id,@s
end
else
begin
declare @sql varchar(100)
select @sql=words+' ' from @tb
where id=@id
update @tb set words=@sql+@s
where id=@id
end
fetch next from roy into @id, @s
end
close roy
deallocate royselect * from @tb id words
----------- -------------------------
1 hello everybody
2 i am programmer(所影响的行数为 2 行)drop table t1
ALTER FUNCTION dbo.combine
(@id varchar(100))
RETURNS varchar (500) AS
BEGIN
declare @s varchar(100)
set @s=''
select @s=@s+''+words from 表名where id=@id
return @s
ENDT-SQL:
select 表名.id,dbo.combine(tb.id) as title
from 表名
group by tb.id
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1
--------------------------------------------
转换成这样的好处是什么啊??
消除冗余、??其他的呢?
select id,case rank when 1 then words end as f,
case rank when 2 then words end as s,
case rank when 3 then words end as t
from T6
) a
group by id
create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1, 'hello', 1)
insert into t1 values(1, 'everybody' , 2)
insert into t1 values(2, 'i' ,1)
insert into t1 values(2, 'am' ,2)
insert into t1 values(2, 'programmer' ,3)create function wsp1(@id int)
returns varchar(1000)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+words from t1 where id=@id
return @sql
endselect distinct id,dbo.wsp1(id) as words from t1
declare @words varchar(128)
declare @rank int
declare @table table(words varchar(128))
set @id = 1
while exists(select * from t1 where id=@id)
begin
set @rank=1
set @words=''
while exists (select * from t1 where id=@id and rank=@rank)
begin
set @words=@words+(select words from t1 where id=@id and rank=@rank)+' '
set @rank=@rank+1
end
insert @table values(@words)
set @id=@id+1
end
select * from @table
create table tb (id int,words varchar(20),rank int)insert into tb(id,words,rank)
select 1,'hello',1
union all select 1,'everybody',2
union all select 2,'i',1
union all select 2,'am',2
union all select 2,'programmer',3-- 创建函数
create function merge_words(@var_id int)
returns varchar(50)
asbegin
declare @string varchar(50)
set @string = ''
select @string = @string + words + ' '
from tb
where id = @var_id
order by rank return @string
end-- 实现
select distinct dbo.merge_words(id)
from tb
-- 测试结果
1 hello everybody
2 i am programmer
declare @words varchar(128)
declare @table table(words varchar(128))
set @id = 1
while exists(select * from t1 where id=@id)
begin
set @words=''
select @words=@words+words+' ' from t1 where id=@id order by rank asc
insert @table values(@words)
set @id=@id+1
end
select * from @table更新了下
insert into tb1(id,words,rank)
select 1,'hello',1
union all select 1,'everybody',2
union all select 2,'i',1
union all select 2,'am',2
union all select 2,'programmer',3
declare @id int
declare @rank int
declare @words varchar(128)
declare @table table(id int,words varchar(128))
set @id = 1
set @rank = 1
set @words=''
while exists(select * from tb1 where id=@id)
begin
while exists(select * from tb1 where id=@id and rank=@rank)
begin
select @words=@words+words+' ' from tb1 where id=@id and rank=@rank
set @rank=@rank+1
end
insert @table values(@id,@words)
set @words=''
set @rank = 1
set @id=@id+1
end
select * from @table