sql查询拆分问题

本文介绍了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