sql server学习

--整理一下如下:如果是分解为各个字段
create table ta(code varchar(10),name varchar(10))
insert into ta values('01', 'A公司')
insert into ta values('02', 'B公司')
create table tb(code varchar(10),linkname varchar(10))
insert into tb values('01', 'aaa')
insert into tb values('01', 'bbb')
insert into tb values('01', 'ccc')
insert into tb values('02', 'ddd')
insert into tb values('02', 'eee')
go

--静态SQL，指一个公司的值最多为3个。
select m.code , m.name ,
  max(case n.px when 1 then n.linkname else '' end) linkname1,
  max(case n.px when 2 then n.linkname else '' end) linkname2,
  max(case n.px when 3 then n.linkname else '' end) linkname3
from ta m ,
(
  select px = (select count(1) from tb where code = t.code and linkname < t.linkname) + 1 , * from tb t
) n
where m.code = n.code
group by m.code , m.name
/*
code       name       linkname1  linkname2  linkname3
---------- ---------- ---------- ---------- ----------
01         A公司        aaa        bbb        ccc
02         B公司        ddd        eee       

(2 行受影响)
*/

--动态SQL，指一个公司的值个数不定。
declare @sql varchar(8000)
set @sql = 'select m.code , m.name'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then linkname else '' '' end) [linkname' + cast(px as varchar) + ']'
from (select distinct px from (select px = (select count(1) from tb where code = t.code and linkname < t.linkname) + 1 , * from tb t) o) as a
set @sql = @sql + ' from ta m , (select px = (select count(1) from tb where code = t.code and linkname < t.linkname) + 1 , * from tb t) n where m.code = n.code group by m.code , m.name'
exec(@sql)
/*code name linkname1 linkname2 linkname3---------- ---------- ---------- ---------- ----------01 A公司 aaa bbb ccc02 B公司 ddd eee(2 行受影响)*/

drop table ta,tb

SQL code

--如果是合为一个串,以下为sql server 2005的写法.create table ta(code varchar(10),name varchar(10))insert into ta values('01', 'A公司') insert into ta values('02', 'B公司') create table tb(code varchar(10),linkname varchar(10))insert into tb values('01', 'aaa') insert into tb values('01', 'bbb') insert into tb values('01', 'ccc') insert into tb values('02', 'ddd') insert into tb values('02', 'eee')goselect m.* , n.linkname from ta m,(  SELECT * FROM(SELECT DISTINCT code FROM tb)A OUTER APPLY(SELECT [linkname]= STUFF(REPLACE(REPLACE((  SELECT linkname FROM tb N WHERE code = A.code FOR XML AUTO), '<N linkname="', ' '), '"/>', ''), 1, 1, ''))N) nwhere m.code = n.code drop table ta,tb/*code       name       linkname---------- ---------- --------------01         A公司        aaa bbb ccc02         B公司        ddd eee(2 行受影响)*/--如果是合为一个串,以下是SQL SERVER 2000的写法,需要一个函数来实现.create table ta(code varchar(10),name varchar(10))insert into ta values('01', 'A公司') insert into ta values('02', 'B公司') create table tb(code varchar(10),linkname varchar(10))insert into tb values('01', 'aaa') insert into tb values('01', 'bbb') insert into tb values('01', 'ccc') insert into tb values('02', 'ddd') insert into tb values('02', 'eee')gocreate function f_hb(@code varchar(10))returns varchar(8000)asbegin  declare @str varchar(8000)  set @str = ''  select @str = @str + ' ' + cast(linkname as varchar) from tb where code = @code   set @str = right(@str , len(@str) - 1)  return(@str)Endgo--调用自定义函数得到结果：select m.* , n.linkname from ta m,(  select distinct code ,dbo.f_hb(code) as linkname from tb) nwhere m.code = n.codedrop table ta,tbdrop function f_hb/*code       name       linkname---------- ---------- -------------01         A公司        aaa bbb ccc02         B公司        ddd eee(2 行受影响)*/