MS SQL同一个字段，多条记录取出后合并成为新字段的问题

[limfungsuen]

SELECT A.*,
(SELECT B.C_CategoryName FROM TE_Category B WHERE 1=1 AND B.C_ID=A.C_ID) AS C_CategoryNameB,
(SELECT C.C_CategoryName FROM TE_Category C WHERE 1=1 AND CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0)AS C_CategoryNameS,
(SELECT D.topic_Name FROM TE_Topic D WHERE 1=1 AND D.topic_ID=A.topic_ID) AS topic_Name
from TE_CorpInfo A
WHERE 1=1 AND CI_CorpName=@CI_CorpName
----------------------
红色加粗部分就是同一个字段，多条记录的部分，（C_ChildID）是一个字符串，C_ID的集合，用逗号分隔的。所以红色部分取到的是多条记录，我怎么把这记录集合并为一条，然后作为C_CategoryNameS一个新的字段

在线等~~OK，马上给分~

[等不到来世]

SELECT A.*, 
(SELECT B.C_CategoryName FROM TE_Category B WHERE 1=1 AND B.C_ID=A.C_ID) AS C_CategoryNameB, 
stuff((SELECT ','+C.C_CategoryName FROM TE_Category C WHERE 1=1 AND CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0 for xml path('')),1,1,'')AS C_CategoryNameS, 
(SELECT D.topic_Name FROM TE_Topic D WHERE 1=1 AND D.topic_ID=A.topic_ID) AS topic_Name 
from TE_CorpInfo A 
WHERE 1=1 AND CI_CorpName=@CI_CorpName 

[水族杰纶]

SELECT A.*,  B.C_CategoryName,C.C_CategoryName,D.topic_Name from  TE_CorpInfo  A
INNER JOIN  TE_Category B ON  B.C_ID=A.C_ID
INNER JOIN  TE_Category C ON CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0
INNER JOIN  TE_Topic D ON D.topic_ID=A.topic_ID

[limfungsuen]

[Quote=引用 2 楼 wufeng4552 的回复:]
SQL code
SELECT A.*, B.C_CategoryName,C.C_CategoryName,D.topic_Name from TE_CorpInfo A
INNER JOIN TE_Category B ON B.C_ID=A.C_ID
INNER JOIN TE_Category C ON CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0
INNER JOIN TE_Topic D ON D.topic_ID=A.topic_ID
[/Quote]

这位大侠没看清我的问题吧

[水族杰纶]

[Quote=引用 3 楼 limfungsuen 的回复:]
引用 2 楼 wufeng4552 的回复:
SQL code
SELECT A.*, B.C_CategoryName,C.C_CategoryName,D.topic_Name from TE_CorpInfo A
INNER JOIN TE_Category B ON B.C_ID=A.C_ID
INNER JOIN TE_Category C ON CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0
INNER JOIN TE_Topic D ON D.topic_ID=A.topic_ID


这位大侠没看清我的问题吧
[/Quote]
整點數據比較直觀~~~

[limfungsuen]

你是直接取的多条记录，我要取的是一条记录，其中一个字段取到 红字部分的所有信息

[等不到来世]

就是字符串合并的问题，参见：

if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab

go

SQL2005用XML:

方法1:

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from 
    (select distinct COl1 from Tab) a
Cross apply
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b

方法2:

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from 
    (select distinct COl1 from Tab) a
cross apply
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)
                .query('<Tab>
                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
                {concat("",string(/Tab[last()]/@COl2))}
                </Tab>')
                )b

SQL05用CTE:

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)


生成结果:
/*
Col1        COl2
----------- ------------
1           a,b,c
2           d,e
3           f

(3 行受影响)
*/

[jiang5311]

帮顶并接分

[cnnbtop]

[Quote=引用 1 楼 szx1999 的回复:]
SQL codeSELECT A.*,
(SELECT B.C_CategoryName FROM TE_Category B WHERE 1=1 AND B.C_ID=A.C_ID) AS C_CategoryNameB,
stuff((SELECT ','+C.C_CategoryName FROM TE_Category C WHERE 1=1 AND CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0 for xml path('')),1,1,'')AS C_CategoryNameS,
(SELECT D.topic_Name FROM TE_Topic D WHERE 1=1 AND D.topic_ID=A.topic_ID) AS topic_Name
from TE_CorpInfo A
WHERE 1=1 AN…
[/Quote]

这个已经实现了楼主的提问了啊～

[tiyuzhongxin789]

(SELECT B.C_CategoryName FROM TE_Category B WHERE 1=1 AND B.C_ID=A.C_ID) AS C_CategoryNameB,
stuff((SELECT ','+C.C_CategoryName FROM TE_Category C WHERE 1=1 AND CHARINDEX(LTRIM(C.C_ID)+',',A.C_ChildID)>0 for xml path('')),1,1,'')AS C_CategoryNameS,
(SELECT D.topic_Name FROM TE_Topic D WHERE 1=1 AND D.topic_ID=A.topic_ID) AS topic_Name
from TE_CorpInfo A
WHERE 1=1 AND CI_CorpName=@CI_CorpName