日期:2014-05-17 浏览次数:20542 次
--你的分太少啦,都有点不想搞
if object_id('A') IS NOT NULL
DROP TABLE A
go
create table A
(
id int,
quanxi nvarchar(20)
)
go
if object_id('B') IS NOT NULL
DROP TABLE B
GO
create table B
(
id int,
wenzi nvarchar(20)
)
go
insert into A
select 1,'1,3,5' union all
select 2,'3,5,8'
go
insert into B
SELECT 1,'查找员工信息' union all
SELECT 2,'删除员工信息' union all
SELECT 3,'添加员工信息' union all
SELECT 4,'更新员工信息' union all
SELECT 5,'查找工作记录' union all
SELECT 6,'删除工作记录' union all
SELECT 7,'添加工作记录' union all
SELECT 8,'更新工作记录'
go
SELECT * FROM a
go
with t as
(
select A.id,B.VALUE from
(
SELECT ID,value=convert(xml,'<root><v>'+replace(quanxi,',','</v><v>')+'</v></root>') from A
)A
outer apply
(
select VALUE = N.V.value('.','VARCHAR(100)') FROM A.[value].nodes('/root/v') N(V)
)b
)
--select t.id,b.id,b.wenzi from t INNER join B on t.value = b.id
,t2 as
(
select t.id,b.id as bid,b.wenzi from t INNER join B on t.value = b.id
)
--select * from t2
select id,
stuff((select ','+wenzi from t2 a where a.id = b.id for xml path('')),1,1,'') '文字' from t2 b
group by id
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-27 20:36:10
-- Version:
-- Microsoft SQL