CREATE TABLE [dbo].[tb]( [id] [int] IDENTITY(1,1) NOT NULL, [txt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [typenum] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]insert into tb(txt,typenum) select 'A','1' union select 'B','1' union select 'C','2' union select 'D','2' union select 'E','1' union select 'F','1' union select 'G','3' union select 'H','2' union select 'I','3'SELECT B.typenum,Result=stuff((select ','+cast(A.txt as varchar(100)) from tb A where A.typenum=B.typenum for xml path('')), 1, 1, '') from tb B group by B.typenumdrop table tb typenum text 1 A,B,E,F 2 C,D,H 3 G,I
这个sql应该不算复杂吧,各位大哥帮忙看下
create table position(compname varchar(10),posname varchar(10)) insert into position select '公司一','A' union select '公司一','B' union select '公司二','C' union select '公司二','D'go create function getCompPos(@compname varchar(10)) returns varchar(2000) as begindeclare @pos varchar(1000) set @pos ='' select @pos=@pos+ posname from position where compname = @compname return @pos end go --调用select a.compname,dbo.getCompPos(a.compname) as 职位 from (select distinct compname from position) AS A
select compname,(select posname+'' from position where compname=aa.compname for xml path('') ) as 职位 from (select distinct compname from position) as aa
那里高手比较多。
[id] [int] IDENTITY(1,1) NOT NULL,
[txt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[typenum] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]insert into tb(txt,typenum)
select 'A','1'
union
select 'B','1'
union
select 'C','2'
union
select 'D','2'
union
select 'E','1'
union
select 'F','1'
union
select 'G','3'
union
select 'H','2'
union
select 'I','3'SELECT B.typenum,Result=stuff((select ','+cast(A.txt as varchar(100)) from tb A
where A.typenum=B.typenum for xml path('')), 1, 1, '') from tb B
group by B.typenumdrop table tb
typenum text
1 A,B,E,F
2 C,D,H
3 G,I
insert into position
select '公司一','A'
union select '公司一','B'
union select '公司二','C'
union select '公司二','D'go create function getCompPos(@compname varchar(10))
returns varchar(2000)
as
begindeclare @pos varchar(1000)
set @pos ='' select @pos=@pos+ posname from position where compname = @compname return @pos
end
go --调用select a.compname,dbo.getCompPos(a.compname) as 职位
from (select distinct compname from position) AS A
from (select distinct compname from position) as aa