本文介绍了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
更多推荐
sql查询拆分问题
发布评论