我們日常開發中，不管是表設計問題抑或是其他什么原因，或多或少都會遇到一張表中有一個字段存儲的內容是用逗號隔開的列表。
具體效果如下圖：
------》
? ? ?從左邊圖轉換成右邊圖，像這種需求，我們難免會遇到。
? ? ?今天我寫了個存儲過程來解決這種問題。主要方式是利用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
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
