sqlserver怎么把一个表中按分号隔开的字符串分成多行放在一个临时表中呢？

[babyli55555]

这个表是一个帖子版区表，第一列是版区id，最后一列是版区负责人，
主要列有：
版区id 版区负责人id
6 150414;1115115156
7 150414;1115115157
有一个人员表，有人员id和人员姓名，和这个表联合查询
最后想形成的表结构是
版区id 版区负责人姓名
6 张三、李四
7 张三、王五


都有什么思路可以实现呢？谢谢大家

[發糞塗牆]

拆了之后匹配完再合并起来

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-17 11:18:55
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([版区id] int,[版区负责人id] nvarchar(34))
insert [huang]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--------------生成数据--------------------------
select
    [版区id], 
    SUBSTRING([版区负责人id],number,CHARINDEX(';',[版区负责人id]+';',number)-number) as [版区负责人id] 
from
    [huang] a,master..spt_values 
where
    number >=1 and number<=len([版区负责人id])  
    and type='p' 
    and substring(';'+[版区负责人id],number,1)=';'
----------------结果----------------------------
/* 
版区id        版区负责人id
----------- ----------------------------------
6           150414
6           1115115156
7           150414
7           1115115157
*/

[--小F--]

帖子版区表的版区负责人id 串 和人员表的ID用CHARINDEX匹配。 合并列值 如果你能给点人员表的数据 可以帮你写写。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-06-17 11:35:40
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([版区id] int,[版区负责人id] varchar(17))
insert [a]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([人员id] int,[人员姓名] varchar(4))
insert [b]
select 150414,'张三' union all
select 1115115156,'李四' union all
select 1115115157,'王五'
--------------开始查询--------------------------
select 
 版区id,
 版区负责人id=stuff((select  ';'+人员姓名 from a ,b  where 版区id=t.版区id and  charindex(';'+ltrim(b.人员id)+';',';'+a.版区负责人id+';')>0  for xml path('')),1,1,'')
from a as t
group by
   版区id
----------------结果----------------------------
/* 版区id        版区负责人id
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6           张三;李四
7           张三;王五

(2 行受影响)
*/

[飞啊子]

 
-- 我也来凑热闹。。 

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([版区id] int,[版区负责人id] varchar(17))
insert [a]
select 6,'150414;1115115156' union all
select 7,'150414;1115115157'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([人员id] int,[人员姓名] varchar(4))
insert [b]
select 150414,'张三' union all
select 1115115156,'李四' union all
select 1115115157,'王五'

--------------开始查询--------------------------
   ;WITH t AS (
	  SELECT a.[版区id],r1.Value userId FROM a
	  CROSS APPLY(
  		SELECT * FROM dbo.[Split](a.[版区负责人id],';') AS s
	  )r1
  )SELECT t.[版区id]
  ,版区负责人姓名=stuff((
  	SELECT '、'+lb.[人员姓名] FROM t lt
  	INNER JOIN b lb ON lt.userId=lb.[人员id]
  	 WHERE lt.[版区id]=t.[版区id]
  	 FOR XML PATH('')
  	 ),1,1,'')
   FROM t GROUP BY t.[版区id]
 
 ------------结果
/*
 
版区id        版区负责人姓名
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6           张三、李四
7           张三、王五

(2 行受影响)
*/


--  dbo.Split方法见

-- http://blog.csdn.net/feiazifeiazi/article/details/17242355