create table tt(id int identity,nuserid varchar(10),ntrade varchar(10)) insert into tt select '58105','001' insert into tt select '58105','001' insert into tt select '58105','006'alter function tt_string (@sql varchar(10)) returns varchar(100) as begin declare @v varchar(100) set @v='' select @v=@v+ntrade+',' from tt where nuserid=@sql set @v=left(@v,len(@v)-1) return @v endcreate view view_tt as select nuserid,dbo.tt_string(nuserid) as ntrade from tt group by nuseridselect * from view_ttnuserid ntrade ---------- ---------------- 58105 001,001,006(1 行受影响)
--带符号合并行列转换--有表t,其数据如下: 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)
nuserid 数据类型为 int 长度 4 ntrade 数据类型为 varchar 长度 50
ojuju10(longdchuanren) : 请问我一运行alter function tt_string (@sql varchar(10)) returns varchar(100) as begin declare @v varchar(100) set @v='' select @v=@v+ntrade+',' from tt where nuserid=@sql set @v=left(@v,len(@v)-1) return @v end提示 服务器: 消息 208,级别 16,状态 6,过程 tt_string,行 10 对象名 'tt_string' 无效。
if exists(select name from sysobjects where name='test' and type='U') drop table [dbo].[test] go create table [dbo].[test] (nuserid varchar(10),ntrade varchar(10)) goinsert into test select '58105','001' union all select '58105','006' union all select '58105','031' union all select '58102','021' union all select '58102','011' union all select '58103','010' go if exists(select name from sysobjects where name='fn_GetUnionStr'and type='FN') drop function [dbo].[fn_GetUnionStr] go create function [dbo].[fn_GetUnionStr] ( @pnuserid varchar(10) ) returns varchar(1000) as begin declare @s varchar(1000) set @s='' select @s=@s+','+ntrade from [dbo].[test] where nuserid=@pnuserid set @s=right(@s,len(@s)-1) return @s end goselect nuserid,[dbo].[fn_GetUnionStr](nuserid)as ntrade from [dbo].[test] group by nuserid/*结果 nuserid ntrade ---------- ----------------- 58102 021,011 58103 010 58105 001,006,031 */
insert into tt select '58105','001'
insert into tt select '58105','001'
insert into tt select '58105','006'alter function tt_string (@sql varchar(10))
returns varchar(100)
as
begin
declare @v varchar(100)
set @v=''
select @v=@v+ntrade+',' from tt
where nuserid=@sql
set @v=left(@v,len(@v)-1)
return @v
endcreate view view_tt as
select nuserid,dbo.tt_string(nuserid) as ntrade from tt
group by nuseridselect * from view_ttnuserid ntrade
---------- ----------------
58105 001,001,006(1 行受影响)
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)
ntrade 数据类型为 varchar 长度 50
returns varchar(100)
as
begin
declare @v varchar(100)
set @v=''
select @v=@v+ntrade+',' from tt
where nuserid=@sql
set @v=left(@v,len(@v)-1)
return @v
end提示
服务器: 消息 208,级别 16,状态 6,过程 tt_string,行 10
对象名 'tt_string' 无效。
drop table [dbo].[test]
go
create table [dbo].[test] (nuserid varchar(10),ntrade varchar(10))
goinsert into test
select '58105','001'
union all select '58105','006'
union all select '58105','031'
union all select '58102','021'
union all select '58102','011'
union all select '58103','010'
go
if exists(select name from sysobjects where name='fn_GetUnionStr'and type='FN')
drop function [dbo].[fn_GetUnionStr]
go
create function [dbo].[fn_GetUnionStr]
(
@pnuserid varchar(10)
)
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+','+ntrade from [dbo].[test] where nuserid=@pnuserid
set @s=right(@s,len(@s)-1)
return @s
end
goselect nuserid,[dbo].[fn_GetUnionStr](nuserid)as ntrade
from [dbo].[test] group by nuserid/*结果
nuserid ntrade
---------- -----------------
58102 021,011
58103 010
58105 001,006,031
*/