create FUNCTION dbo.getPlus(@ID int)
RETURNS varchar(800)
AS
BEGIN
DECLARE @str varchar(800)
set @str=''
SELECT @str=@str+rtrim(Name)+','
FROM [dbo].[tablename]
where ID = @ID
order by str_ID
return @str
ENDselect [ID],getPlus(ID) from [table]
RETURNS varchar(800)
AS
BEGIN
DECLARE @str varchar(800)
set @str=''
SELECT @str=@str+rtrim(Name)+','
FROM [dbo].[tablename]
where ID = @ID
order by str_ID
return @str
ENDselect [ID],getPlus(ID) from [table]
自定义函数:
create function f_getclient(@id int )return varchar(50) as
begin
declare @clientname varchar(50)
select @clientname =name from table_name where id =@id and name like '%供应商%'
return @clientname
end
Create Table TEST
(ID Int,
Name Nvarchar(20))
Insert TEST Values(1,N'A客户')
Insert TEST Values(1,N'B供应商')
Insert TEST Values(2,N'C客户')
Insert TEST Values(2,N'D供应商')
GO
--建立函数
Create Function GetName(@ID Int)
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Set @S=''
Select @S=@S+','+Name from TEST Where ID=@ID Order By Name
Return (Stuff(@S,1,1,''))
End
GO
--测试
Select ID,dbo.GetName(ID) As Name from TEST Group By ID
GO
--删除测试环境
Drop Table TEST
Drop Function GetName
--结果
/*
ID Name
1 A客户,B供应商
2 C客户,D供应商
*/
insert into tb select 1,'A客户'
union select 1,'B供应商'
union select 2,'C客户'
union select 2,'D供应商' create FUNCTION dbo.getname(@as_ID int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @str varchar(1000)
set @str=''
SELECT @str=@str+','+rtrim(Name)
FROM tb
where ID = @as_ID
return stuff(@str,1,1,'')
ENDselect [ID],dbo.getname(ID) from tb group by id
结果:
ID
----------- -----------------------------------------------------------------------------
1 A客户,B供应商
2 C客户,D供应商(所影响的行数为 2 行)
RETURNS varchar(800)
AS
BEGIN
DECLARE @str varchar(800)
set @str=''
SELECT @str=@str+','+ltrim(rtrim(Name))
FROM [dbo].[tablename]
where ID = @ID
order by str_ID
set @str=stuff(@str,1,1,'')
return @str
ENDselect [ID],getPlus(ID) from [table]---没测试
create table tab([id] char(1000),[name] char (1000))
insert into tab
select '1','A客户' union all
select '1','B供应商' union all
select '2','C客户' union all
select '2','D供应商'
CREATE FUNCTION dbo.get123(@ID int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @a varchar(1000)
set @a=''
SELECT @a=@a+','+rtrim([Name])
FROM tab
where [ID] = @ID
return stuff(@a,1,1,'')
ENDselect [ID],dbo.get123(ID) from tab group by [id]--ID
------------------ ----------
1 A客户,B供
2 C客户,D供(所影响的行数为 2 行)
drop table tempif exists(select 1 from sysobjects where name='temp_view' and type='v')
drop view temp_viewif exists(select 1 from sysobjects where name='uf_temp' and type='FN')
drop function uf_temp
gocreate table temp(id int,name varchar(20))
gocreate function dbo.uf_temp(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+name from temp where id=@id
if @str<>''
set @str=right(@str,len(@str)-1)
return @str
endgo
insert into temp
select 1,'A客户' union
select 1,'B供应商' union
select 2,'C客户' union
select 2,'D供应商' goselect * from temp
go
/*
id name
1 A客户
1 B供应商
2 C客户
2 D供应商*/--select id from temp group by id
create view temp_view
as
select id,dbo.uf_temp(id) name from temp group by id
goselect * from temp_view
/*
id name
1 A客户,B供应商
2 C客户,D供应商*/
2 D供应商,C客户这可能不符合实际的要求~~
2,C客户,D供应商 意识是 ID,AName,BName
1,A客户,B供应商
2,C客户,D供应商
但是无法在视图中使用临时表,这里仅做参考:create table #t(Pid int identity,id int,name varchar(100))insert into #t select * from tbselect a.id,a.name,b.name
from #t a,#t b
where a.id=b.id and a.name<>b.name and a.Pid%2=1drop table #t
生成的视图结构是:ID,AName,BName 即 1,A客户,B供应商 对应三列!比较急~
RETURNS varchar(800)
AS
BEGIN
DECLARE @str varchar(800)
set @str=''
SELECT @str=@str+rtrim(Name)+','
FROM [dbo].[tablename]
where ID = @ID
order by ID
return @str
ENDselect [ID],getPlus(ID) from [table]
--建立测试环境
if exists(select 1 from sysobjects where id=object_id('A') and xtype='U')
drop table A
go
create table A
(
ID Int,
Name nvarchar(20)
)
insert A values(1,N'A客户')
insert A values(1,N'B供应商')
insert A values(2,N'C客户')
insert A values(2,N'D供应商')
go--创建视图
if exists(select 1 from sysobjects where id=object_id('v') and xtype='V')
drop view v
go
create view v
as
select T1.ID,[Name]=T2.name+','+T1.name
from
(select ID,[Name]=max(name) from A group by ID)T1
join
(select ID,[Name]=min(name) from A group by ID)T2
on T1.ID=T2.IDgo --查看
select * from v--删除测试环境
drop table A
drop view v--结果
/*
ID Name
----------- -----------------------------------------
1 A客户,B供应商
2 C客户,D供应商(所影响的行数为 2 行)
*/
select 1,'A客户' union
select 1,'B供应商' union
select 2,'C客户' union
select 2,'D供应商' gocreate view tempview asselect t1.id,t1.name aname ,t2.name bname
from
(select * from temp
where name like '%客户'
)t1
join
(select * from temp
where name like '%供应商'
)t2
on t1.id =t2.id
goselect * from tempviewdrop table temp
drop view tempview/*1 A客户 B供应商
2 C客户 D供应商
*/
三列啊~
if exists(select 1 from sysobjects where id=object_id('A') and xtype='U')
drop table A
go
create table A
(
ID Int,
Name nvarchar(20)
)
insert A values(1,N'A客户')
insert A values(1,N'B供应商')
insert A values(2,N'C客户')
insert A values(2,N'D供应商')
go--创建视图
if exists(select 1 from sysobjects where id=object_id('v') and xtype='V')
drop view v
go
create view v
as
select T1.ID,[Name1]=T2.name,[Name2]=T1.name
from
(select ID,[Name]=max(name) from A group by ID)T1
join
(select ID,[Name]=min(name) from A group by ID)T2
on T1.ID=T2.IDgo --查看
select * from v--删除测试环境
drop table A
drop view v--结果
/*ID Name1 Name2
----------- -------------------- --------------------
1 A客户 B供应商
2 C客户 D供应商(所影响的行数为 2 行)
*/
create view view1
as
select a.id, min(a.name) name1, max(b.name) name2
from table a inner join (select * from table) b on a.id=b.id
group by a.id
go
好像樓主不是那個意思
我的理解是
ID 客戶 供应商
1 A客户 B供应商
2 C客户 D供应商不知道是不是這個意思
最近一個多月的工作實豐是太忙了。
---建立测试环境:create table table1(id int,name varchar(10))
insert into table1 select 1,'A客户'
union select 1,'B供应商'
union select 2,'C客户'
union select 2,'D供应商' select * from table1
---创建满足条件的视图create view a
as
select a.id id ,min(a.name) aname,b.name bname from table1 a
join (select id,max(name)name from table1 group by id )b
on a.id=b.id group by a.id,b.name----显示所需的数据
select * from a
TO: paoluo(一天到晚游泳的鱼)
好像樓主不是那個意思
我的理解是
ID 客戶 供应商
1 A客户 B供应商
2 C客户 D供应商不知道是不是這個意思
--------------
是,楼主是这个意思。只是他开始那样写的,大家都误会了。
正确答案来了:select a.[ID], [ProviderName],[ClientName]
Form (
Select [ID],[Name] as [ProviderName] from [table] where [Name] like '%供应商%'
) as a
Inner Join
(
Select [ID],[Name] as [ClientName] from [table] where [Name] like '%客户%'
) as b
on a.[ID]=b.[ID]
Order by a.[ID]
select id,min(name),max(name) from table1 group by id,简单明了.反正楼主要求的视图只两列,正好一个max一个min
select id,
(select top 1 name from table1 a where a.id=b.id and a.name like '%客户%'),
(select top 1 name from table1 a where a.id=b.id and a.name like '%供应商%') from table1 b group by id