sql查询拆分问题

问题描述：

我有一个标准化的表格，包含这样的数据

I have a normalized table having data like this

Gid         Gname

1           dance

2           cricket

3           football

在我的表中我保存这样的数据

In my table i am saving data like this

ID   GID     SID

1    1,3     101

2     3      102

3    1,2,3   103

我想要这样的输出

I want output like this

SID   GName

101   dance,football

102   football

103   dance,cricket,football

谁可以在sql 2008中编写此查询

Who can i write this query in sql 2008

答

你可以用两种方式来做...使用 Fnsplitter 函数或联接...

U can do it in two ways... Either Using Fnsplitter Function or joins...

Create Table #Temp
	(
	Id int, 
	Gid Nvarchar(40),
	Sid Nvarchar(40) 
	)

Create Table #Dept
	(
	Gid Nvarchar(40),
	Gname Nvarchar(40) 
	)

Insert into #Temp 
Select 1,'1,3','101' Union all
Select 2,'3','102' Union all
Select 3,'1,2,3','103' 

Insert into #Dept 
Select '1','dance' Union All
Select '2','cricket' Union All
Select '3','football'

-- If u have fnsplitter u can use this....
--Select t.Sid,Stuff((Select ','+Gname From #Dept 
--		      Where Gid In (Select ID From fnSplitter(t.Gid)) 
--	For XML Path(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') [Gname] 
--From #Temp t

-- With out Fnsplitter
SELECT e.Sid, STUFF((SELECT ',' + d.Gname FROM #Dept AS d
		     INNER JOIN #Temp AS t
		   ON ',' + t.Gid + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.Gid) + ',%'
		   WHERE t.Gid = e.Gid
		   ORDER BY Gname
		   FOR XML PATH, TYPE).value('.', 'nvarchar(max)'), 1, 1, '') as Gname
FROM #Temp AS e
ORDER BY Sid;

Drop Table #Temp
Drop Table #Dept

输出：

Output:

Sid	Gname
-----   ---------
101	dance,football
102	football
103	cricket,dance,football

相关推荐