分拆列值1

/*

标题:分拆列值1
描述
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

*/
--
1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY ( int , 1 , 1 ) INTO # FROM syscolumns a, syscolumns b

SELECT A.id, SUBSTRING (A. [ values ] , B.id, CHARINDEX ( ' , ' , A. [ values ] + ' , ' , B.id) - B.id) FROM tb A, # B WHERE SUBSTRING ( '
, ' + A. [ values ] , B.id, 1 ) = ' , '
DROP
TABLE #
-- 2. 新的解决方法(sql server 2005)
create
table tb(id int ,value varchar ( 30 ))
insert into tb values ( 1 , ' aa,bb ' )
insert into tb values ( 2 , ' aaa,bbb,ccc ' )
go

SELECT A.id, B.value FROM ( SELECT id, [ value ] = CONVERT (xml, ' <root><v> ' + REPLACE ( [ value ] , ' , ' , ' </v><v> ' ) +
' </v></root> ' ) FROM tb)A OUTER APPLY( SELECT value = N.v.value( ' . ' , ' varchar(100) ' )
FROM A. [ value ] .nodes( ' /root/v ' ) N(v))B
DROP TABLE tb

/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/

---------------------------------------------=======================

--第二种!






IF

OBJECT_ID
(
'
tb
'
)
IS

NOT

NULL



DROP

TABLE
tb


GO



CREATE

TABLE
tb (id
INT
,col
VARCHAR
(
30
))


INSERT

INTO
tb
VALUES
(
1
,
'
aa,bb
'
)


INSERT

INTO
tb
VALUES
(
2
,
'
aaa,bbb,ccc
'
)


GO



--
1.2000/2005通用方法



SELECT


a.id,

col

=
SUBSTRING
(a.col,
number
,
CHARINDEX
(
'
,
'
,a.col
+
'
,
'
,
number
)
-
b.
number
)


FROM
tb a


JOIN
master..spt_values b


ON
b.type
=
'
p
'



--
AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可




AND

CHARINDEX
(
'
,
'
,
'
,
'
+
a.col,
number
)
=
number



--
结果:


/*



id col

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc



(5 行受影响)


*/





--
2.2005以上新方法:



SELECT
a.id,b.col


FROM
(


SELECT
id,col
=
CAST
(
'
<v>
'
+
REPLACE
(col,
'
,
'
,
'
</v><v>
'
)
+
'
</v>
'

AS
XML)


FROM
tb

)

AS
a


OUTER
APPLY (


SELECT
C.value(
'
.
'
,
'
varchar(50)
'
)
AS
col
--
此处value必须为小写




FROM
a.col.nodes(
'
/v
'
)
AS
T(C)

)

AS
b


--
结果:


/*



id col

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc



(5 行受影响)


*/





--
3.游标循环法:



DECLARE

@t

TABLE
(id
INT
,col
NVARCHAR
(
50
))


DECLARE

@id

INT
,
@col

nvarchar
(
200
)


DECLARE
c
CURSOR

FOR

SELECT

*

FROM
tb


OPEN
c


FETCH

NEXT

FROM
c
INTO

@id
,
@col



WHILE

@@FETCH_STATUS

=

0



BEGIN



WHILE

CHARINDEX
(
'
,
'
,
@col
)
>
0



BEGIN



INSERT

@t

SELECT

@id
,
LEFT
(
@col
,
CHARINDEX
(
'
,
'
,
@col
+
'
,
'
)
-
1
)


SET

@col
=
STUFF
(
@col
,
1
,
CHARINDEX
(
'
,
'
,
@col
),
''
)


END



INSERT

@t

SELECT

@id
,
LEFT
(
@col
,
CHARINDEX
(
'
,
'
,
@col
+
'
,
'
)
-
1
)
--
退出循环后插入最后获取的值




FETCH

NEXT

FROM
c
INTO

@id
,
@col



END



CLOSE
c


DEALLOCATE
c


--
查询



SELECT

*

FROM

@t



--
结果:


/*



id col

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc



(5 行受影响)


*/





--
4.SQL2005 函数法:


/*

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

-- Author : 分拆列值(函数法) htl258(Tony)

-- Date : 2009-09-10 01:38:02

-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

Mar 29 2009 10:27:29

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

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

*/



IF

OBJECT_ID
(
'
tb
'
)
IS

NOT

NULL



DROP

TABLE
tb


GO



CREATE

TABLE
tb (id
INT
,col
VARCHAR
(
30
))


INSERT

INTO
tb
VALUES
(
1
,
'
aa,bb
'
)


INSERT

INTO
tb
VALUES
(
2
,
'
aaa,bbb,ccc
'
)


GO





IF

OBJECT_ID
(
'
f_str
'
)
IS

NOT

NULL



DROP

FUNCTION
f_str


GO



CREATE

FUNCTION
f_str(
@str

VARCHAR
(
20
))


RETURNS

@t

TABLE
(col
VARCHAR
(
20
))


AS



BEGIN



SET

@str
=
@str
+
'
,
'



WHILE

len
(
@str
)
>
0



BEGIN



INSERT

@t

SELECT

LEFT
(
@str
,
CHARINDEX
(
'
,
'
,
@str
)
-
1
)


SET

@str
=
STUFF
(
@str
,
1
,
CHARINDEX
(
'
,
'
,
@str
),
''
)


END



RETURN



END



GO





--
调用查询



SELECT
a.id,b.col


FROM
tb a


CROSS
APPLY f_str(a.col) b


/*


id col

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc



(5 行受影响)


*/





--
5.SQL2005函数法二:


/*

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

-- Author : 分拆列值(函数法) htl258(Tony)

-- Date : 2009-09-10 01:46:02

-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

Mar 29 2009 10:27:29

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

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

*/



IF

OBJECT_ID
(
'
tb
'
)
IS

NOT

NULL



DROP

TABLE
tb


GO



CREATE

TABLE
tb (id
INT
,col
VARCHAR
(
30
))


INSERT

INTO
tb
VALUES
(
1
,
'
aa,bb
'
)


INSERT

INTO
tb
VALUES
(
2
,
'
aaa,bbb,ccc
'
)


GO





IF

OBJECT_ID
(
'
f_str
'
)
IS

NOT

NULL



DROP

FUNCTION
f_str


GO



CREATE

FUNCTION
f_str(
@str

VARCHAR
(
50
))


RETURNS

@t

TABLE
(col
VARCHAR
(
50
))


AS



BEGIN



DECLARE

@xml
XML


SET

@xml
=
'
<v>
'
+
REPLACE
(
@str
,
'
,
'
,
'
</v><v>
'
)
+
'
</v>
'



INSERT

@t

SELECT
C.value(
'
.
'
,
'
varchar(50)
'
)
FROM

@xml
.nodes(
'
/v
'
)
AS
T(C)


RETURN



END



GO





--
调用查询



SELECT
a.id,b.col


FROM
tb a


CROSS
APPLY f_str(a.col) b


/*


id col

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)


*/





©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页