参考:
http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.9349634
http://expert.csdn.net/Expert/topic/2440/2440306.xml?temp=.9349634
解决方案 »
- SQL查询语句求解!
- iBatis连SQLServer2008
- sql数据库中的数据的累加问题
- 大家好,请介绍几本学SQL SERVER的书.我有点基础,想做开发
- 求一条SQL!!!!!!!!!!!!!!!!!!!
- 如何根据当前日期得到上个月的月初和月末的日期?
- 急!!sql存储过程中遇到个错误 实在找不到原因请大家 在线等
- 这种方式形成的SQL表,如果需要修改数据该怎么修改
- ORA-01041 internal error. hostdef extension doesn't exist
- 求SQL语句,UPDATE批量更新
- 急阿,就50分了,全给,请教关于重装安装sql,
- 一个关于多表连接的问题,急
--用函数
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+Description+',' from 表 where ID=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end--执行
select distinct ID,dbo.test(ID) as Description from 表
[交流]行列转换
--用函数
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+Description+',' from 表 where ID=@id
set @sql=left(@sql,len(@sql)-1)
return(@sql)
end--执行
select distinct ID,dbo.test(ID) as Description from 表
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+[Description] from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
create table 表A(id int,[description] varchar(20))
insert 表A select 1,'Hello'
union all select 1,'Good Morning'
union all select 2,'Peter'
union all select 2,'Tome'
union all select 2,'Sean'
go
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+[Description] from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) as [description] from 表A
/*结果
id description
----------- ------------------
1 Hello,Good Morning
2 Peter,Tome,Sean
*/
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
returns varchar(8000)
as
begin
declare @Description varchar(8000)
set @Description = ''
select @Description = @Description + ',' + Description from A where id=@id
select @Description=right(@Description,len(@Description)-1)
return @Description
end
gocreate table A(id int,Description varchar(30))
insert A
select 1,'Hello'
union select 1,'Good Morning'
union select 2,'Peter'
union select 2,'Tome'
union select 2,'Sean'
select * from Aselect id,dbo.f_hb(id) from A Group by iddrop table A
drop function f_hb