数据表中有个字段有保存多个ID，如何一起显示
ID IDs Name
1 .1.2.3. 张1
2 .3. 张2
3 .2.3. 张3
4 .1.3. 张4
如何查询出如下格式：
ID IDs IDsName Name
1 .1.2.3. .张1.张2.张3. 张1
[解决办法]
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-11-20 17:53:40
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
--Feb 10 2012 19:39:15
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[IDs] varchar(7),[Name] varchar(3))
insert [tb]
select 1,'.1.2.3.','张1' union all
select 2,'.3.','张2' union all
select 3,'.2.3.','张3' union all
select 4,'.1.3.','张4'
--------------开始查询--------------------------
SELECT
[id],ids,name,
idsname=stuff((select '.'+LTRIM(Name) from tb WHERE CHARINDEX('.'+LTRIM(id)+'.','.'+a.ids+'.')>0 for xml path('')), 1, 1, '')
FROM
TB a
GROUP BY
[id],ids,name
----------------结果----------------------------
/* id ids name idsname
----------- ------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 .1.2.3. 张1 张1.张2.张3
2 .3. 张2 张3
3 .2.3. 张3 张2.张3
4 .1.3. 张4 张1.张3
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[IDs] varchar(7),[Name] varchar(3))
insert [tb]
select 1,'.1.2.3.','张1' union all
select 2,'.3.','张2' union all
select 3,'.2.3.','张3' union all
select 4,'.1.3.','张4'
select ID,IDs,
STUFF(
(
select '.'+ t2.Name
from tb t2
where CHARINDEX('.'+cast(t2.ID as varchar)+'.',t1.IDs)>0
for xml path('')
),
1,1,''
) as idsname,
Name
from tb t1
/*
IDIDs idsname Name
1.1.2.3.张1.张2.张3张1
2.3. 张3 张2
3.2.3.张2.张3 张3
4.1.3.张1.张3 张4
*/