行转列
代码 类型 名称
12 AB 15D
12 AB 48D
12 A 59
31 AB C
31 A 59D现在要按代码查出类型为AB的所有名称
显示为
12 15D,48D
31 C
下面是我做的.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOALTER function ROWTOCOL(@COLUMNNAME VARCHAR(100),@CONDITION VARCHAR(10))
returns varchar(8000)
as
begin
declare @rect varchar(8000)
declare @ssql varchar(8000)
set @rect = ''
select @rect = @rect+','+(CASE WHEN LEN(ITPROFILE1)<1 THEN COMPANYNAME ELSE ITPROFILE1 END) FROM ITPROFILE WHERE COMPANYCODE IN (SELECT BONDSDT1 from bondsdt where bondcode = @columnname AND BONDSDT2=@CONDITION)
set @rect = stuff(@rect,1,1,'')
return @rect
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSELECT DISTINCT BONDCODE,DBO.ROWTOCOL(BONDCODE,'资金保管机构') FROM BONDSDT WHERE BONDSDT2='资金保管机构' GROUP BY BONDCODE这么写会漏数据,查出来的是空的.
可以确保ITPROFILE表内数据的完整性.将函数内的语句单独运行,执行是没有错的.可以取到值.高手帮忙啊.
代码 类型 名称
12 AB 15D
12 AB 48D
12 A 59
31 AB C
31 A 59D现在要按代码查出类型为AB的所有名称
显示为
12 15D,48D
31 C
下面是我做的.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOALTER function ROWTOCOL(@COLUMNNAME VARCHAR(100),@CONDITION VARCHAR(10))
returns varchar(8000)
as
begin
declare @rect varchar(8000)
declare @ssql varchar(8000)
set @rect = ''
select @rect = @rect+','+(CASE WHEN LEN(ITPROFILE1)<1 THEN COMPANYNAME ELSE ITPROFILE1 END) FROM ITPROFILE WHERE COMPANYCODE IN (SELECT BONDSDT1 from bondsdt where bondcode = @columnname AND BONDSDT2=@CONDITION)
set @rect = stuff(@rect,1,1,'')
return @rect
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSELECT DISTINCT BONDCODE,DBO.ROWTOCOL(BONDCODE,'资金保管机构') FROM BONDSDT WHERE BONDSDT2='资金保管机构' GROUP BY BONDCODE这么写会漏数据,查出来的是空的.
可以确保ITPROFILE表内数据的完整性.将函数内的语句单独运行,执行是没有错的.可以取到值.高手帮忙啊.
insert test
select '12','AB','15D' union all
select '12','AB','48D' union all
select '12','A','59' union all
select '31','AB','C' union all
select '31','A','59D'
select * from test
gocreate function Gettxt(@id int,@lx varchar(20))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +名称 from test where 代码=@id and 类型=@lx
--return @s
return stuff(@s,1,1,'')
end
goselect 代码,dbo.Gettxt(代码,'AB') from test group by 代码drop function Gettxt
drop table test