表合并

[sp_maoqi]

1．表：ljs(列解释)

lpyjc(列拼音简称) lhymc(列汉语名称) lsm(列说明)
cdid 菜单id 说明
2．表：lszb(列所在表)
lid(列id) lszbbm(列所在表表名) lpyjc(列拼音简称)
1 cdb cdid
2 bbjbpzb cdid
3 bbcolpzb cdid

要求：
lpyjc(列拼音简称) lhymc(列汉语名称) lsm(列说明) lszbbm(列所在表表名)
cdid 菜单id 说明 cdb, bbjbpzb, bbcolpzb

[xyxfly]

create function test(@col varchar(100))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+','+[lszbbm(列所在表表名)] from lszb
return(stuff(@s,1,1,''))
end
go
select a.*,b.[lszbbm(列所在表表名)] into #T from lszb b left join ljs a on a.[lpyjc(列拼音简称)]=b.[lpyjc(列拼音简称)]
select a.[lpyjc(列拼音简称)],min(a.[lhymc(列汉语名称)]) [lhymc(列汉语名称)],min(a.[lsm(列说明)]) [lsm(列说明)],[lszbbm(列所在表表名)]=dbo.test([lpyjc(列拼音简称)]) from #T a group by [lpyjc(列拼音简称)]

[liangpei2008]

CREATE TABLE ljs(lpyjc varchar(20),lhymc varchar(20),lsm varchar(20))
INSERT into ljs SELECT 'cdid','菜单id','说明'
go
CREATE TABLE lszb(lid int,lszbbm varchar(20),lpyjc varchar(20))
INSERT into lszb SELECT 1,'cdb','cdid'
UNION all SELECT 2,'bbjbpzb','cdid'
UNION all SELECT 3,'bbcolpzb','cdid'

--创建用户定义函数
CREATE FUNCTION f_str(@lpyjc varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @ret varchar(8000)
SET @ret = ''
SELECT @ret = @ret+','+lszbbm from lszb where lpyjc = @lpyjc
SET @ret = stuff(@ret,1,1,'')
RETURNS @ret
END
go

--执行
SELECT a.*,b.szbbm
FROM ljs A INNER JOIN
(SELECT lpyjc,szbbm=dbo.f_str(lpyjc) FROM lszb GROUP by lpyjc ) b
on a.lpyjc=b.lpyjc
go

[jackluo1981]

CREATE FUNCTION f_str(@lpyjc varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @ret varchar(8000)
SET @ret = ''
SELECT @ret = @ret+','+lszbbm from lszb where lpyjc = @lpyjc
SET @ret = stuff(@ret,1,1,'')
RETURNS @ret
END
go

--执行
SELECT a.*,b.szbbm
FROM ljs A INNER JOIN
(SELECT distin ctlpyjc,szbbm=dbo.f_str(lpyjc) FROM lszb ) b
on a.lpyjc=b.lpyjc
go