我们日常开发中，不管是表设计问题抑或是其他什么原因，或多或少都会遇到一张表中有一个字段存储的内容是用逗号隔开的列表。
具体效果如下图：
------》
从左边图转换成右边图，像这种需求，我们难免会遇到。
今天我写了个存储过程来解决这种问题。主要方式是利用master..spt_values表。
具体存储过程如下：
--
Author: LHM
--
Create date: 2015-01-10
--
Description: 把表中某一个列按照逗号拼接列表
--
示例： EXEC [Sp_StringsToTable] 'AgentId','UserId','Bse_GeneralAgent',''
--
=============================================
CREATE
PROCEDURE
[
dbo
]
.
[
Sp_StringsToTable
]
@ColumnId
VARCHAR
(
100
) ,
@ColumnName
VARCHAR
(2047
) ,
@TableName
NVARCHAR
(
100
) ,
@Filter
VARCHAR
(
1000
)
=
''
AS
BEGIN
DECLARE
@sql
VARCHAR
(
500
)
IF
(
@Filter
<>
''
)
BEGIN
SET
@Sql
=
'
select
'
+
@ColumnId
+
'
, RTRIM( LTRIM( substring(
'
+
@ColumnName
+
'
+
''
,
''
,a.number,charindex(
''
,
''
,
'
+
@ColumnName
+
'
+
''
,
''
,a.number+1)-a.number)) ) Id
from master..spt_values a,
'
+
@TableName
+
'
b
where
'
+
@Filter
+
'
and a.type=
''
p
''
and substring(
''
,
''
+
'
+
@ColumnName
+
'
,a.number,1)=
''
,
''
'
END
ELSE
BEGIN
SET
@Sql
=
'
select
'
+
@ColumnId
+
'
, RTRIM( LTRIM( substring(
'
+
@ColumnName
+
'
+
''
,
''
,a.number,charindex(
''
,
''
,
'
+
@ColumnName
+
'
+
''
,
''
,a.number+1)-a.number)) ) Id
from master..spt_values a,
'
+
@TableName
+
'
b
where a.type=
''
p
''
and substring(
''
,
''
+
'
+
@ColumnName
+
'
,a.number,1)=
''
,
''
'
END
EXEC
(
@Sql
)
END
这个存储过程有一个限制：就是@ColumnName的值不能超过2047个字节，也就是说，图中的UserId的字段里面的内容不能超过2047个字符。
原因就是因为master..spt_values表的限制。大家可以在数据库中执行 SELECT * FROM master..spt_values type='p' 就可以知道限制的原因了。
有兴趣的朋友可以 试着建立如图的表
CREATE
TABLE
[
dbo
]
.
[
Bse_GeneralAgent
]
(
[
AgentId
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
,
[
UserId
]
[
varchar
]
(
max
)
NULL
,
CONSTRAINT
[
PK_Bse_GeneralAgent
]
PRIMARY
KEY
CLUSTERED
(
[
AgentId
]
ASC
)
WITH
(PAD_INDEX
=
OFF
, STATISTICS_NORECOMPUTE
=
OFF
, IGNORE_DUP_KEY
=
OFF
, ALLOW_ROW_LOCKS
=
ON
, ALLOW_PAGE_LOCKS
=
ON
)
ON
[
PRIMARY
]
)
ON
[
PRIMARY
]
GO
随意添加一些测试数据进行测试 。只需执行存储过程
EXEC [Sp_StringsToTable] 'AgentId','UserId','Bse_GeneralAgent',''
希望给遇到此类需求的朋友带来帮助，谨此记录。
如果觉得有用，可以推荐一下，谢谢。
------------------------------------------------------------以下是 指尖流淌 的思路，感谢---------------------------------------------------
--
Author: LHM
--
Create date: 2015-01-10
--
Description: 把表中某一个列按照逗号拼接列表
--
示例： EXEC Sp_StringsToTableExtend 'AgentId','UserId','Bse_GeneralAgent'
--
=============================================
CREATE
PROCEDURE
[
dbo
]
.Sp_StringsToTableExtend
@ColumnId
VARCHAR
(
MAX
) ,
@ColumnName
VARCHAR
(
MAX
) ,
@TableName
NVARCHAR
(
100
)
AS
BEGIN
DECLARE
@sql
VARCHAR
(
500
)
SET
@Sql
=
'
SELECT A.
'
+
@ColumnId
+
'
, B.StrColumn
FROM (SELECT StrXml = CONVERT(XML,
''
<root><v>
''
+REPLACE(
'
+
@ColumnName
+
'
,
''
,
''
,
''
</v><v>
''
)+
''
</v></root>
''
) ,
'
+
@ColumnId
+
'
, UserId FROM
'
+
@TableName
+
'
) A OUTER APPLY (SELECT StrColumn = N.v.value(
''
.
''
,
''
nvarchar(40)
''
) FROM A.StrXml.nodes(
''
/root/v
''
) N (v) ) B
'
EXEC
(
@Sql
)
END
GO
