SQL请教 如下图 怎么将上表拆成下表？
请教大神如何用SQL把上表拆成下表样子
部位数不固定的
SQL sql
[解决办法]
用spt_values
http://bbs.csdn.net/topics/390599689
[解决办法]
create table #ta(ID int,部位 varchar(50))
insert into #ta
select '10001','头部,腹部'
with cte as
(
select ID,部位+',' 部位
from #ta
),cte1 as
(
select ID,left(部位,CHARINDEX(',',部位)-1) as a,
right(部位,len(部位)-CHARINDEX(',',部位))b
from cte
union all
select ID,left(b,CHARINDEX(',',b)-1) as a,
right(b,len(b)-CHARINDEX(',',b))b
from cte1
where len(b)>1
)
select ID,a from cte1
------------------------------------------------
ID a
----------- ---------------------------------------------------
10001 头部
10001 腹部
(2 行受影响)
CREATE function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
/*--实现split功能的函数*/
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
GO
WITH cte AS
(
SELECT 10001 AS ID,'头部 腹部' AS 部位
)
SELECT ID=ROW_NUMBER() OVER(ORDER BY GETDATE()),*
FROM
(
SELECT ID AS [r-ID],b.a AS 部位
FROM cte CROSS APPLY dbo.f_split(部位,' ') b
) T
--IDr-ID部位
--1100001头部
--2100001腹部
--create table #ta(ID int,部位 varchar(50))
--insert into #ta
--select '10001','头部,腹部'
with cte as
(
select ID,部位+',' 部位
from #ta
),cte1 as
(
select ID,left(部位,CHARINDEX(',',部位)-1) as a,
right(部位,len(部位)-CHARINDEX(',',部位))b
from cte
union all
select ID,left(b,CHARINDEX(',',b)-1) as a,
right(b,len(b)-CHARINDEX(',',b))b
from cte1
where len(b)>1
)
select ROW_NUMBER()over(order by id)ID,ID r_ID,a from cte1
----------------------------------------------------
ID r_ID a
-------------------- ----------- ---------------------------------------------------
1 10001 头部
2 10001 腹部
(2 行受影响)
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-17 17:01:37
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[部位] varchar(9))
insert [huang]
select 10001,'头部 腹部'
--------------开始查询--------------------------
SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())id,
id AS 'r-id',
SUBSTRING([部位],number,CHARINDEX(' ',[部位]+' ',number)-number) as [部位]
from
[huang] a,master..spt_values
where
number >=1 and number<len([部位])
and type='p'
and substring(' '+[部位],number,1)=' '
----------------结果----------------------------
/*
id r-id 部位
-------------------- ----------- ---------
1 10001 头部
2 10001 腹部
*/
if OBJECT_ID('tb') is not null drop table tb
create table tb(id varchar(5),[部位] varchar(20))
insert into tb(id,[部位])
select '10001','头部 腹部'
select ROW_NUMBER()over(order by getdate())as ID,id as r_ID,substring([部位],b.number,charindex(' ',[部位]+' ',b.number)-b.number) as [部位]
from tb a with(nolock),master..spt_values b with(nolock)
where b.number>=1 and b.number<len(a.[部位]) and b.type='P'
and substring(' '+[部位],number,1)=' '
drop table tb
ID r_ID 部位
-------------------- ----- --------------------
1 10001 头部
2 10001 腹部
(2 行受影响)
--通过递归产生序列
;with t
as
(
select 1 as number
union all
select number + 1 as number
from t
where t.number < 100
)
select ROW_NUMBER()over(order by getdate())as ID,
id as r_ID,
substring([部位],b.number,charindex(' ',[部位]+' ',b.number)-b.number) as [部位]
from tb a with(nolock),t b
where b.number>=1 and b.number<len(a.[部位])
and substring(' '+[部位],number,1)=' '
option(maxrecursion 100)
/*
IDr_ID部位
110001头部
210001腹部
*/
