我們?nèi)粘ｉ_發(fā)中，不管是表設計問題抑或是其他什么原因，或多或少都會遇到一張表中有一個字段存儲的內(nèi)容是用逗號隔開的列表。
具體效果如下圖：
------》
? ? ?從左邊圖轉(zhuǎn)換成右邊圖，像這種需求，我們難免會遇到。
? ? ?今天我寫了個存儲過程來解決這種問題。主要方式是利用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個字節(jié)，也就是說，圖中的UserId的字段里面的內(nèi)容不能超過2047個字符。
?原因就是因為master..spt_values表的限制。大家可以在數(shù)據(jù)庫中執(zhí)行 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
?
?隨意添加一些測試數(shù)據(jù)進行測試 。只需執(zhí)行存儲過程
?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
?
更多文章、技術(shù)交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
