A表 字段
iCwid
1
2
3B表 字段
IWKWID iCwid keyword
1 1 abc
2 1 bcd
3 1 abdc
4 2 ad
5 3 asda怎样将2个表放到一个视图里C
iCwid keyword
1 abc bcd abdc
2 ad
3 asda
iCwid
1
2
3B表 字段
IWKWID iCwid keyword
1 1 abc
2 1 bcd
3 1 abdc
4 2 ad
5 3 asda怎样将2个表放到一个视图里C
iCwid keyword
1 abc bcd abdc
2 ad
3 asda
(SELECT iCwid, stuff((SELECT ' ' + X.keyword FROM B表 AS X WHERE X.iCwid=Y.iCwid FOR xml path('')), 1, 1, '') AS keyword
FROM B表 AS Y GROUP BY Y.iCwid) AS A
INNER JOIN A表 AS B
ON A.iCwid=B.iCwid
http://www.cnblogs.com/lilwzca/archive/2011/11/17/2253043.html
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
create function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
return @str
end
go
--调用函数
select id , value = dbo.f_str(id) from tb group by id42 43 drop function dbo.f_str44 drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '') from tb group by id
drop table tb
FROM (SELECT DISTINCT iCwid FROM B) A OUTER APPLY(
SELECT [keywords]= STUFF(REPLACE(REPLACE(
(
SELECT keyword FROM B N
WHERE iCwid = A.iCwid
FOR XML AUTO
), '<N keyword="', ','), '"/>', ''), 1, 1, '')
)N
FROM (SELECT DISTINCT iCwid FROM tb) A OUTER APPLY(
SELECT [keywords]= STUFF(REPLACE(REPLACE(
(
SELECT keyword FROM tb N
WHERE iCwid = A.iCwid
FOR XML AUTO
), '<N keyword="', ','), '"/>', ''), 1, 1, '')
)N
(SELECT iCwid, stuff((SELECT ' ' + X.keyword FROM B表 AS X WHERE X.iCwid=Y.iCwid FOR xml path('')), 1, 1, '') AS keyword
FROM B表 AS Y GROUP BY Y.iCwid) AS A
INNER JOIN A表 AS B
ON A.iCwid=B.iCwid
引用上1楼的,试试看!