我們?nèi)粘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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
