/* Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 愿和大家共同进步 如有雷同、实属巧合 ●●●●●2009-09-04 15:19:28.123●●●●● ★★★★★soft_wsx★★★★★ */ if object_ID('TB') IS NOT NULL DROP TABLE TB create table tb(id int,corpname varchar(10),salebrand varchar(10)) insert tb select 1, '化成公司', '阿迪' union all select 2, '化成公司', '耐克' union all select 3, '化成公司', '奔驰' union all select 4, '天意公司', '宝马' union all select 5, '长空公司', '奥迪' go if object_id('f_tb') is not null drop function f_tb go create function f_tb(@corpname nvarchar(100)) returns nvarchar(100) as begin declare @sql nvarchar(4000) set @sql=N'' select @sql=@sql+N','+salebrand from tb where corpname=@corpname set @sql=stuff(@sql,1,1,N'') return(@sql) end goif object_id('f_tb2') is not null drop function f_tb2 go create function f_tb2(@corpname nvarchar(100)) returns nvarchar(100) as begin declare @sql nvarchar(4000) set @sql=N'' select @sql=@sql+N','+cast(id as varchar) from tb where corpname=@corpname set @sql=stuff(@sql,1,1,N'') return(@sql) end goselect dbo.f_tb2(corpname) as 公司代码,corpname as 公司,dbo.f_tb(corpname) as 经营品牌 from tb group by corpname order by 公司代码
If not object_id('[tb]') is null Drop table [tb] Go Create table [tb](id int,name varchar(16), [type] varchar(16)) Insert [tb] Select 1, 'dd' , '1,2' union all Select 2, 'ddd', '1' union all Select 3, 'ee' , '2' If not object_id('[tb1]') is null Drop table [tb1] Go Create table [tb1](id int,name varchar(16)) Insert [tb1] Select 1, '画家' union all select 2 , '作家' if object_id('ff')is not null drop function ff go create function dbo.ff(@id int) returns varchar(400) as begin declare @str varchar(400) set @str=''
select @str=isnull(@str+',','')+b.name from tb a,tb1 b where charindex(','+ltrim(b.id)+',',','+type+',')>0 and a.id=@id set @str=stuff(@str,1,1,'') return @str end goselect id,name,dbo.ff(id)from tbid name ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 dd 画家,作家 2 ddd 画家 3 ee 作家(3 行受影响)
use dbif(object_id('tb1') is not null) drop table tb1 if(object_id('tb2') is not null) drop table tb2create table tb1(id int identity(1,1),[type] varchar(10)) insert tb1 select '1,2' union all select '2'create table tb2(id int, [name] varchar(50)) insert tb2 select 1,'一' union all select 2,'二'go alter function dbo.fun(@str varchar(10)) returns varchar(50) as begin declare @sql varchar(50) set @sql='' select @sql=@sql+', '+[name] from tb2 where patindex('%'+cast(id as varchar)+'%',@str)>0 set @sql=stuff(@sql,1,1,'') return @sql end goselect id,dbo.fun([type])as [typename] from tb1 order by id/* id typename 1 一, 二 2 二 */
create table tb (a varchar(10),b varchar(10)) insert tb select '2009', 'AA' union all select '2009', 'BB' union all select '2008', 'CC' union all select '2007', 'XX' union all select '2009', 'HH' union all select '2008', 'DD' union all select '2007', 'SS' union all select '2006', 'GG' go select * from tb CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(b as varchar) FROM tb WHERE a=@col1 RETURN(STUFF(@re,1,1,'')) END GOselect a,dbo.f_str(a)[b] from tb group by a a b ---------- ------------------- 2006 GG 2007 XX,SS 2008 CC,DD 2009 AA,BB,HH(所影响的行数为 4 行)
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-04 15:19:28.123●●●●●
★★★★★soft_wsx★★★★★
*/
if object_ID('TB') IS NOT NULL DROP TABLE TB
create table tb(id int,corpname varchar(10),salebrand varchar(10))
insert tb
select 1, '化成公司', '阿迪'
union all select 2, '化成公司', '耐克'
union all select 3, '化成公司', '奔驰'
union all select 4, '天意公司', '宝马'
union all select 5, '长空公司', '奥迪'
go
if object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@corpname nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+salebrand from tb where corpname=@corpname
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goif object_id('f_tb2') is not null drop function f_tb2
go
create function f_tb2(@corpname nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+cast(id as varchar) from tb where corpname=@corpname
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goselect dbo.f_tb2(corpname) as 公司代码,corpname as 公司,dbo.f_tb(corpname) as 经营品牌 from tb
group by corpname
order by 公司代码
/*
公司代码 公司 经营品牌
1,2,3 化成公司 阿迪,耐克,奔驰
4 天意公司 宝马
5 长空公司 奥迪
*/昨天的
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb](id int,name varchar(16), [type] varchar(16))
Insert [tb] Select
1, 'dd' , '1,2' union all Select
2, 'ddd', '1' union all Select
3, 'ee' , '2' If not object_id('[tb1]') is null
Drop table [tb1]
Go
Create table [tb1](id int,name varchar(16))
Insert [tb1] Select
1, '画家' union all select
2 , '作家' if object_id('ff')is not null drop function ff
go
create function dbo.ff(@id int)
returns varchar(400)
as
begin
declare @str varchar(400)
set @str=''
select @str=isnull(@str+',','')+b.name
from tb a,tb1 b
where charindex(','+ltrim(b.id)+',',','+type+',')>0 and a.id=@id
set @str=stuff(@str,1,1,'') return @str
end
goselect id,name,dbo.ff(id)from tbid name
----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 dd 画家,作家
2 ddd 画家
3 ee 作家(3 行受影响)
use dbif(object_id('tb1') is not null)
drop table tb1
if(object_id('tb2') is not null)
drop table tb2create table tb1(id int identity(1,1),[type] varchar(10))
insert tb1
select '1,2' union all
select '2'create table tb2(id int, [name] varchar(50))
insert tb2
select 1,'一' union all
select 2,'二'go
alter function dbo.fun(@str varchar(10))
returns varchar(50)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+', '+[name] from tb2 where patindex('%'+cast(id as varchar)+'%',@str)>0
set @sql=stuff(@sql,1,1,'')
return @sql
end
goselect id,dbo.fun([type])as [typename] from tb1 order by id/*
id typename
1 一, 二
2 二
*/
create table tb (a varchar(10),b varchar(10))
insert tb
select '2009', 'AA' union all
select '2009', 'BB' union all
select '2008', 'CC' union all
select '2007', 'XX' union all
select '2009', 'HH' union all
select '2008', 'DD' union all
select '2007', 'SS' union all
select '2006', 'GG'
go
select * from tb
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(b as varchar)
FROM tb
WHERE a=@col1
RETURN(STUFF(@re,1,1,''))
END
GOselect a,dbo.f_str(a)[b] from tb group by a a b
---------- -------------------
2006 GG
2007 XX,SS
2008 CC,DD
2009 AA,BB,HH(所影响的行数为 4 行)