問題描述

使用 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 中由一個字符拆分的多個列連接在一起的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！