sqlserver表列字段逗号转行_sqlserver 字段以逗号分隔成多行数据,该如何解决

sqlserver 字段以逗号分隔成多行数据

直接上图

目前数据结构已经满足：

1、两个字段如果按照逗号分割的话，数组长度是一样的。

2、两个字段按逗号分隔成数组后，通过相同下标取到的值是对应的。

求一个sql，让查询出来的格式为

code       code2

a1        a1a1a1a1

a2        a2a2a2a2

b1        b1b1b1b1

b2        b2b2b2b2

------解决思路----------------------

试下，出现不妥的地方，我再调整--模拟数据表

IF OBJECT_ID('TempDB..#TB',N'U') IS NOT NULL

DROP TABLE TempDB..#TB

GO

CREATE TABLE TempDB..#TB(

code varchar(100)

,code2 varchar(8000)

)

INSERT INTO #TB

SELECT 'a1,a2','a1a1a1a1,a2a2a2a2'

UNION ALL

SELECT 'b1,b2','b1b1b1b1,b2b2b2b2'

GO

--查询语句开始

WITH CTE AS(

SELECT

doc = CONVERT(xml,''+REPLACE(code,',','')+''),

doc2 = CONVERT(xml,''+REPLACE(code2,',','')+'')

FROM #TB

)

,CTE2 AS(

SELECT

ROW_NUMBER()OVER(ORDER BY GETDATE()) RN

,A.x.value('.','varchar(10)') code

FROM

CTE

CROSS APPLY doc.nodes('//v') AS A(x)

)

,CTE3 AS(

SELECT

ROW_NUMBER()OVER(ORDER BY GETDATE()) RN

,A.x.value('.','varchar(10)') code2

FROM

CTE

CROSS APPLY doc2.nodes('//v') AS A(x)

)

SELECT

A.code

,B.code2

FROM

CTE2 A

LEFT JOIN CTE3 B ON A.RN=B.RN

--查询语句结束

------解决思路----------------------

----------------------------------------------------------------

-- Author  :DBA_HuangZJ(發糞塗牆)

-- Date    :2014-09-23 07:48:19

-- Version:

--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

--May 14 2014 18:34:29

--Copyright (c) Microsoft Corporation

--Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

--

----------------------------------------------------------------

--> 测试数据：[huang]

if object_id('[huang]') is not null drop table [huang]

go

create table [huang]([code] varchar(5),[code2] varchar(17))

insert [huang]

select 'a1,a2','a1a1a1a1,a2a2a2a2' union all

select 'b1,b2','b1b1b1b1,b2b2b2b2'

--------------开始查询--------------------------

;WITH cte AS

(

SELECT row_number()OVER(ORDER BY code)id,code,code2

FROM huang

),cte1 AS (

SELECT id,

SUBSTRING([code],number,CHARINDEX(',',[code]+',',number)-number) as [code]

from

cte a,master..spt_values

where

number >=1 and number<=len([code])

and type='p'

and substring(','+[code],number,1)=','),cte2 AS

(SELECT id,

SUBSTRING([code2],number,CHARINDEX(',',[code2]+',',number)-number) as [code2]

from

cte a,master..spt_values

where

number >=1 and number<=len([code2])

and type='p'

and substring(','+[code2],number,1)=',')

SELECT cte1.code,cte2.code2

FROM cte1 INNER JOIN cte2 ON cte2.id = cte1.id AND cte1.code=LEFT(cte2.code2,2)

----------------结果----------------------------

/*

code  code2

----- -----------------

a1    a1a1a1a1

a2    a2a2a2a2

b1    b1b1b1b1

b2    b2b2b2b2

*/

------解决思路----------------------

WITH a0 (code,code2) AS

(

select 'a1,a2','a1a1a1a1,a2a2a2a2' union all

select 'b1,b2','b1b1b1b1,b2b2b2b2'

)

,a1 AS

(

SELECT *,ROW_NUMBER() OVER(ORDER BY @@servername) id

FROM a0

)

,a2 AS

(

SELECT a.id,b.code,b.id2

FROM

(

SELECT id,code=CONVERT(XML, ''+replace(RTRIM(LTRIM(code)),',','')+'')

FROM a1

) a

OUTER APPLY

(SELECT code = C.v.value('.','NVARCHAR(MAX)'),id2=ROW_NUMBER() OVER(ORDER BY @@servername) FROM a.code.nodes('/root/v') C(v)) b

)

,a3 AS

(

SELECT a.id,b.code2,b.id2

FROM

(

SELECT id,code2=CONVERT(XML, ''+replace(RTRIM(LTRIM(code2)),',','')+'')

FROM a1

) a

OUTER APPLY

(SELECT code2 = C.v.value('.','NVARCHAR(MAX)'),id2=ROW_NUMBER() OVER(ORDER BY @@servername) FROM a.code2.nodes('/root/v') C(v)) b