SQL分组查询。

Question

一个表中数据如下
省(State)市(city)       区县(Distrinct)
山西省 大同    曹夫楼村、齐家坡村、梓家村、白马城村、马家小村
山西省 忻州 忻州市 下属原平市、符村广宇煤电、邮政信箱
山西省 忻州 定襄县
山西省 忻州 五台县
山西省 忻州 代县
山东省 青州 乡镇及村组
…………………………………………还有很多，全国各地的数据都在里面我想得到这样的数据
省        市        区县
山西省 大同    曹夫楼村
山西省 大同    齐家坡村
山西省 大同    梓家村
山西省 大同    白马城村
山西省 大同    马家小村
山西省 忻州    原平市
山西省 忻州    符村广宇煤电
山西省 忻州    邮政信箱
山西省 忻州 定襄县
山西省 忻州 五台县
山西省 忻州 代县
山东省 青州 乡镇及村组
请问如何写？

Answer 1

--参考
拆分表:--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-05-06 17:48:55
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([State] varchar(6),[city] varchar(4),[Distrinct] varchar(46),[C4] varchar(34))
insert [tb]
select '山西省','大同','曹夫楼村、齐家坡村、梓家村、白马城村、马家小村',null union all
select '山西省','忻州','忻州市','下属原平市、符村广宇煤电、邮政信箱' union all
select '山西省','忻州','定襄县',null union all
select '山西省','忻州','五台县',null union all
select '山西省','忻州','代县',null union all
select '山东省','青州','乡镇及村组',null
--------------开始查询--------------------------
Select
    a.State,a.city,Distrinct=substring(a.Distrinct,b.number,charindex('、',a.Distrinct+'、',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.Distrinct)
where
     substring('、'+a.Distrinct,b.number,1)='、'----------------结果----------------------------
/* State  city Distrinct
------ ---- ----------------------------------------------
山西省    大同   曹夫楼村
山西省    大同   齐家坡村
山西省    大同   梓家村
山西省    大同   白马城村
山西省    大同   马家小村
山西省    忻州   忻州市
山西省    忻州   定襄县
山西省    忻州   五台县
山西省    忻州   代县
山东省    青州   乡镇及村组(10 行受影响)*/

Answer 3

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (State varchar(6),city varchar(4),Distrinct varchar(100))
insert into [tb]
select '山西省','大同','曹夫楼村、齐家坡村、梓家村、白马城村、马家小村' union all
select '山西省','忻州','忻州市、下属原平市、符村广宇煤电、邮政信箱' union all
select '山西省','忻州','定襄县' union all
select '山西省','忻州','五台县' union all
select '山西省','忻州','代县' union all
select '山东省','青州','乡镇及村组'Select a.State,a.city,Distrinct=substring(a.Distrinct,b.number,charindex('、',a.Distrinct+'、',b.number)-b.number) 
from  tb a join master..spt_values  b ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.Distrinct)
where substring('、'+a.Distrinct,b.number,1)='、'

Answer 4

多谢各位。。今天问了个EXL表格达人。。直接在EXL中间就弄好了，好方便。哈哈