在sql2000中求一条sql语句，行转列并且合并显示

2010-05-11 10:55:42 1.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

luzhiyonghello 墨衍会员 · AI 创作全网分发关注 + 加入社区

合并多条记录的同一字段值SQL语句 1.生成测试数据： create table team(name varchar(32),person varchar(32)); insert into team values('A','Tom'); insert into team values('A','Jim'); insert into team values('A','Anny'); insert into team val 阅读详情

create table stuobject(
id int primary key identity (1,1) ,
stuname varchar(10),
proname varchar(20)
)
insert into stuobject values('张三','语文')
insert into stuobject values('张三','数学')

insert into stuobject values('王五','语文')
insert into stuobject values('王五','化学')
insert into stuobject values('王五','数学')
insert into stuobject values('王五','英语')

insert into stuobject values('李四','英语')
insert into stuobject values('李四','语文')
insert into stuobject values('李四','物理')
要求查出的结果如下显示，不用存储过程实现，一条sql语句查询即可

姓名所学课程

张三语文,数学
王五语文,化学,数学,英语
李四英语,语文,物理

...全文

华强北手表256G真相大揭秘：ADB命令实测内存与存储（附完整调试教程）本文通过ADB命令实战揭秘华强北智能手表256G存储等硬件参数的真实性。文章提供了从开启调试模式到使用ADB命令查询系统属性、验证内存与存储容量的完整教程，帮助用户识别参数虚标，评估设备真实性能与潜在风险。阅读详情

luzhiyonghello

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

luzhiyonghello 2010-05-12

打赏
举报

哈哈，问题解决了，看来这里高手挺多的，结帖散分去了，呵呵

ws_hgo 2010-05-12

打赏
举报

create table stuobject(

  id int primary key identity (1,1) ,

  stuname varchar(10),

  proname varchar(20)

)

insert into stuobject values('张三','语文')

insert into stuobject values('张三','数学')



insert into stuobject values('王五','语文')

insert into stuobject values('王五','化学')

insert into stuobject values('王五','数学')

insert into stuobject values('王五','英语')



insert into stuobject values('李四','英语')

insert into stuobject values('李四','语文')

insert into stuobject values('李四','物理')





create function dbo.getName(@Name nvarchar(30))

returns nvarchar(100)

as

begin

  declare @str nvarchar(100)

  set @str=''

  select @str=@str+proname+',' from stuobject where stuname=@Name

  return stuff(@str,1,1,'')

end



select distinct stuname,dbo.getName(stuname) 所学课程 from stuobject



stuname    所学课程

---------- ----------------------------------------------------------------------------------------------------

李四         语,语文,物理,

王五         文,化学,数学,英语,

张三         文,数学,



(3 行受影响)

zyj_604 2010-05-12

打赏
举报

例子

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[t_test1]

GO



CREATE TABLE [dbo].[t_test1] (

	[Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,

	[Subject] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,

	[Result] [int] NULL 

) ON [PRIMARY]

GO





declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

 from (select distinct Subject from t_test1) as a

select @sql = @sql+' from t_test1 group by name'

exec(@sql)

永生天地 2010-05-12

打赏
举报

还是贴个例子，lz可以留着研究

/*

标题：按某字段合并字符串之一(简单合并)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-11-06

地点：广东深圳



描述：将如下形式的数据按id字段合并value字段。

id    value

----- ------

1     aa

1     bb

2     aaa

2     bbb

2     ccc

需要得到结果：

id     value

------ -----------

1      aa,bb

2      aaa,bbb,ccc

即：group by id, 求 value 的和（字符串相加）

*/

--1、sql2000中只能用自定义的函数解决

create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go



create function dbo.f_str(@id int) returns varchar(100)

as

begin

    declare @str varchar(1000)

    set @str = ''

    select @str = @str + ',' + cast(value as varchar) from tb where id = @id

    set @str = right(@str , len(@str) - 1)

    return @str

end

go



--调用函数

select id , value = dbo.f_str(id) from tb group by id



drop function dbo.f_str

drop table tb





--2、sql2005中的方法

create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go



select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')

from tb

group by id



drop table tb





--3、使用游标合并数据

create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go

declare @t table(id int,value varchar(100))--定义结果集表变量

--定义游标并进行合并处理

declare my_cursor cursor local for

select id , value from tb

declare @id_old int , @id int , @value varchar(10) , @s varchar(100)

open my_cursor

fetch my_cursor into @id , @value

select @id_old = @id , @s=''

while @@FETCH_STATUS = 0

begin

    if @id = @id_old

       select @s = @s + ',' + cast(@value as varchar)

    else

      begin

        insert @t values(@id_old , stuff(@s,1,1,''))

        select @s = ',' + cast(@value as varchar) , @id_old = @id

      end

    fetch my_cursor into @id , @value

END

insert @t values(@id_old , stuff(@s,1,1,''))

close my_cursor

deallocate my_cursor



select * from @t

drop table tb

百年树人 2010-05-11

打赏
举报



--SQL2000用函数

create function [dbo].[f_str](@stuname varchar(20))

returns varchar(50)

as

begin

  declare @s varchar(800)

  select @s=isnull(@s+',','')+proname

  from stuobject

  where stuname=@stuname 

  return @s

end

go



-->查询

select 

stuname as 姓名,

所学课程=dbo.f_str(stuname)

from stuobject 

group by stuname



/**

姓名         所学课程

---------- --------------------------------------------------

李四         英语,语文,物理

王五         语文,化学,数学,英语

张三         语文,数学



(3 行受影响)





**/

htl258_Tony 2010-05-11

打赏
举报

create table stuobject(

  id int primary key identity (1,1) ,

  stuname varchar(10),

  proname varchar(20)

)

insert into stuobject values('张三','语文')

insert into stuobject values('张三','数学')



insert into stuobject values('王五','语文')

insert into stuobject values('王五','化学')

insert into stuobject values('王五','数学')

insert into stuobject values('王五','英语')



insert into stuobject values('李四','英语')

insert into stuobject values('李四','语文')

insert into stuobject values('李四','物理')

go



create function fn_fstr(@name varchar(20))

returns varchar(100)

as

begin

	declare @s varchar(100)

	select @s=isnull(@s+',','')+proname from stuobject where stuname=@name

	return @s

end

go



select distinct stuname,dbo.fn_fstr(stuname) proname from stuobject

/*

stuname    proname

---------- ----------------------------------------------------------------------------------------------------

李四         英语,语文,物理

王五         语文,化学,数学,英语

张三         语文,数学



(3 行受影响)

*/

百年树人 2010-05-11

打赏
举报

create table stuobject(

  id int primary key identity (1,1) ,

  stuname varchar(10),

  proname varchar(20)

)

insert into stuobject values('张三','语文')

insert into stuobject values('张三','数学')



insert into stuobject values('王五','语文')

insert into stuobject values('王五','化学')

insert into stuobject values('王五','数学')

insert into stuobject values('王五','英语')



insert into stuobject values('李四','英语')

insert into stuobject values('李四','语文')

insert into stuobject values('李四','物理')





select stuname as 姓名,

所学课程=stuff((select ','+proname from stuobject where stuname=t.stuname for xml path('')),1,1,'')

from stuobject t

group by stuname



/**

姓名         所学课程

---------- ---------------------------------------

李四         英语,语文,物理

王五         语文,化学,数学,英语

张三         语文,数学



(3 行受影响)



**/

百年树人 2010-05-11

打赏
举报

select stuname as 姓名,

所学课程=stuff((select ','+proname from stuobject where stuname=t.stuname for xml path('')),1,1,'')

from stuobject t

group by stuname

在sql2000中求一条sql语句 ，行转列并且合并显示

相关推荐

在sql2000中求一条sql语句，行转列并且合并显示