将在 SQL 中由一个字符拆分的多个列连接在一起 [英] Join together multiple columns split by a character in SQL

问题描述

使用 mssql，如果我有如下数据:

列:id、名称、list1、list21, '第一', '10;15;30;50', '25;12;15;18'2, '秒', '50;30;15;10, '12;25;11;15'...10,'第十','9;2;15;1','5;13;17;45'

我正在尝试创建将每个列表列连接在一起的结果行，例如

1, 'first', 10, 251, '第一', 15, 121, '第一个', 30, 151, '第一', 50, 182, '秒', 50, 122, '秒', 30, 252, '第二', 15, 112, '秒', 10, 15...10, '第十', 9, 510, '第十', 2, 1310, '第十', 15, 1710, '第十', 1, 45

基本上，每个列表的每个数字都映射到该索引处的相同数字(由;"分割).我可以使用 cross apply + string_split，但它会为每个可能的组合生成一行(id * description * list1_size * list2_size)这在 sql 中甚至可能吗?

我也尝试过使用 substring + charindex 手动移动列表，但这会导致大量手动列.

解决方案

如果列表大小相等:

SELECT 1 AS id, 'first' AS name, '10;15;30;50' AS list1, '25;12;15;18' AS list2进入联合所有选择 2, '秒', '50;30;15;10', '12;25;11;15';-- 有点不确定，ROW_NUMBER 按占位符 1/0 排序选择 ID、名称、s1.value、s2.value从TCROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) AS r FROM STRING_SPLIT(list1, ';')) s1CROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) AS r FROM STRING_SPLIT(list2, ';')) s2哪里 s1.r = s2.r;

db<>小提琴演示/p>

相关:STRING_SPLIT 添加返回行号的选项

---

使用 OPENJSON 获取元素在数组中的确定位置:

SELECT id, name, A.value, B.value从TCROSS APPLY(选择值，[key] AS rnFROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list1,';',','),']')))) ACROSS APPLY(选择值，[key] AS rnFROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list2,';',','),']')))) B其中 A.rn = B.rn;

db<>fiddle 演示 2

---

编辑 2:

不同尺寸的处理清单:

WITH cte1 AS (SELECT id, name, A.value, A.rn从TCROSS APPLY(选择值，[key] AS rnFROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list1,';',','),']')))) A),cte2 AS (SELECT id, name, A.value, A.rn从TCROSS APPLY(选择值，[key] AS rnFROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list2,';',','),']')))) A)SELECT id = COALESCE(cte1.id, cte2.id),name = COALESCE(cte1.name, cte2.name),cte1.value,cte2.value从 cte1完全加入 cte2ON cte1.id = cte2.idAND cte1.rn = cte2.rn按 id 排序；

db<>fiddle 演示 3

using mssql, if i have data such as:

cols: 
id, name,     list1,         list2
1, 'first',  '10;15;30;50', '25;12;15;18'
2, 'second', '50;30;15;10,  '12;25;11;15' 
...
10,'tenth',  '9;2;15;1',    '5;13;17;45'

im trying to create rows of results that join each of those list columns together, such as

1, 'first', 10, 25
1, 'first', 15, 12
1, 'first', 30, 15
1, 'first', 50, 18
2, 'second', 50, 12
2, 'second', 30, 25
2, 'second', 15, 11
2, 'second', 10, 15
...
10, 'tenth', 9, 5
10, 'tenth', 2, 13
10, 'tenth', 15, 17
10, 'tenth', 1, 45

basically, each number of each list maps to the same number at that index (split by ';'). i'm able to use cross apply + string_split, but it results in a row for each possible combination (id * description * list1_size * list2_size) is this even possible in sql?

I've also tried using substring + charindex to manually move around the lists, but this would result in an exorbitant amount of manual columns.

解决方案

If the list have equal size:

SELECT 1 AS id, 'first' AS name, '10;15;30;50' AS list1, '25;12;15;18' AS list2
INTO t
UNION ALL
SELECT 2, 'second', '50;30;15;10', '12;25;11;15';

-- a bit undeterministic, ROW_NUMBER ordered by placeholder 1/0
SELECT id, name, s1.value, s2.value
FROM t
CROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) AS r FROM STRING_SPLIT(list1, ';')) s1
CROSS APPLY (SELECT *, ROW_NUMBER() OVER(ORDER BY 1/0) AS r FROM STRING_SPLIT(list2, ';')) s2
WHERE s1.r = s2.r;

db<>fiddle demo

Related: STRING_SPLIT Add Option to Return Row Number

---

EDIT:

Using OPENJSON to get deterministic position of element in array:

SELECT id, name, A.value, B.value
FROM t
CROSS APPLY (SELECT value, [key] AS rn 
             FROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list1,';',','),']')))) A
CROSS APPLY (SELECT value, [key] AS rn 
             FROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list2,';',','),']')))) B
WHERE A.rn = B.rn;

db<>fiddle demo 2

---

EDIT 2:

Handling list of different sizes:

WITH cte1 AS (
  SELECT id, name, A.value, A.rn
  FROM t
  CROSS APPLY (SELECT value, [key] AS rn 
              FROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list1,';',','),']')))) A
),cte2 AS (
  SELECT id, name, A.value, A.rn
  FROM t
  CROSS APPLY (SELECT value, [key] AS rn 
              FROM OPENJSON(JSON_QUERY(CONCAT('[',REPLACE(t.list2,';',','),']')))) A
)
SELECT id = COALESCE(cte1.id, cte2.id)
       ,name = COALESCE(cte1.name, cte2.name)
       ,cte1.value
       ,cte2.value
FROM cte1
FULL JOIN cte2
  ON cte1.id = cte2.id
 AND cte1.rn = cte2.rn
ORDER BY id;

db<>fiddle demo 3

这篇关于将在 SQL 中由一个字符拆分的多个列连接在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！