在列中拆分逗号分隔值 [英] Splitting of comma separated values in a column

问题描述

我被要求创建一个能够执行以下操作的功能：



我有三列：

I have been asked to create a function that will do the following:

I have three columns:

Parent Number...............SNDDI............................................DDI
12457847549........24578487557,5559864127
54876458722..........................................................54685913487,46589857855

我需要分开SNDDI& DDI编号并与父编号放在一行如下，编号类型（编号来自哪一列）

I need to seperate the SNDDI & the DDI numbers and put in a row with the parent number as below and type of number(which column the number came from)

Parent Number.....SNDDI.................DDI..............TYPE
---------------------------------------------------------------
12457847549.......24578487557............................SNDDI
12457847549.......5559864127.............................SNDDI
12457847549.......22645134996............................SNDDI
54876458722.............................54685913487......DDI
54876458722.............................46589857855......DDI

推荐答案

我真的建议你使用公用表格式 [ ^ ]。



试试这个：

I really do recommend you to use Common Table Expressions[^].

Try this:

DECLARE @tmp TABLE ([Parent Number] BIGINT, SNDDI VARCHAR(255), DDI VARCHAR(255))

INSERT INTO @tmp ([Parent Number], SNDDI, DDI)
SELECT 12457847549 AS [Parent Number], '24578487557,5559864127' AS SNDDI, '' AS DDI
UNION ALL SELECT 54876458722, '', '54685913487,46589857855'

--SELECT *
--FROM @tmp

;WITH sndis AS
(
	SELECT [Parent Number], LEFT(SNDDI, CHARINDEX(',', SNDDI)-1) AS ChildNumber, 'SNDDI' AS TypeOfNumber, RIGHT(SNDDI, LEN(SNDDI)- CHARINDEX(',', SNDDI)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', SNDDI)>0
	UNION ALL
	SELECT [Parent Number], LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ChildNumber, 'SNDDI' AS TypeOfNumber, RIGHT(Remainder, LEN(Remainder)- CHARINDEX(',', Remainder)) AS Remainder
	FROM sndis
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT [Parent Number], Remainder AS ChildNumber, 'SNDDI' AS TypeOfNumber, NULL AS Remainder
	FROM sndis
	WHERE CHARINDEX(',', Remainder)=0
), ddis AS
(
	SELECT [Parent Number], LEFT(DDI, CHARINDEX(',', DDI)-1) AS ChildNumber, 'DDI' AS TypeOfNumber, RIGHT(DDI, LEN(DDI)- CHARINDEX(',', DDI)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', DDI)>0
	UNION ALL
	SELECT [Parent Number], LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ChildNumber, 'DDI' AS TypeOfNumber, RIGHT(Remainder, LEN(Remainder)- CHARINDEX(',', Remainder)) AS Remainder
	FROM ddis
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT [Parent Number], Remainder AS ChildNumber, 'DDI' AS TypeOfNumber, NULL AS Remainder
	FROM ddis
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT [Parent Number], ChildNumber, TypeOfNumber
FROM (
	SELECT [Parent Number], ChildNumber, TypeOfNumber
	FROM sndis
	UNION ALL
	SELECT [Parent Number], ChildNumber, TypeOfNumber
	FROM ddis
	) AS T

结果：

Result:

Parent Number   ChildNumber     TypeOfNumber
12457847549	24578487557	SNDDI
12457847549	5559864127	SNDDI
54876458722	54685913487	DDI
54876458722	46589857855	DDI

更多：

使用公用表表达式 [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

这不是您所需要的 - 您的要求非常具体 - 但它应该给您一个想法如何开始处理它：在SQL中使用逗号分隔值参数字符串IN条款 [ ^ ]

This isn't exactly what you need - your requirement is pretty specific - but it should give you an idea how to start handling it: Using comma separated value parameter strings in SQL IN clauses[^]

您好试试这个代码块

Hi try this code block

DECLARE @tmp TABLE ([Parent Number] BIGINT, SNDDI VARCHAR(255), DDI VARCHAR(255))

 INSERT INTO @tmp ([Parent Number], SNDDI, DDI)
 SELECT 12457847549 AS [Parent Number],'24578487557,5559864127' AS SNDDI,'' AS DDI
 UNION ALL
 SELECT 54876458722, '', '54685913487,46589857855'


;WITH SplitSNDDISCTE AS (
    SELECT [Parent Number],CAST('<i>' + REPLACE(SNDDI, ',', '</i><i>') + '</i>' AS XML) AS
    SNDDIS,DDI FROM @tmp
    ),
    SplitDDICTE AS (
        SELECT [Parent Number],x.i.value('.', 'VARCHAR(MAX)') AS SNDDI,
    CASE WHEN LTRIM(RTRIM(x.i.value('.', 'VARCHAR(MAX)'))) <> ''
        THEN 'SNDDI' ELSE 'DDI' END As Type,
        CAST('<i>'+ REPLACE(DDI,',','</i><i>') +'</i>' AS XML) AS DDIS
        FROM SplitSNDDISCTE CROSS APPLY SNDDIS.nodes('//i') x(i)
    )

   SELECT [Parent Number],SNDDI,a.i.value('.','VARCHAR(MAX)') AS DDI,Type
   FROM SplitDDICTE CROSS APPLY DDIS.nodes('//i') a(i)

这篇关于在列中拆分逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！