问题描述
使用 mssql,如果我有如下数据:
我正在尝试创建将每个列表列连接在一起的结果行,例如
基本上,每个列表的每个数字都映射到该索引处的相同数字(由;"分割).我可以使用 cross apply
+ string_split
,但它会为每个可能的组合生成一行(id * description * list1_size * list2_size)这在 sql 中甚至可能吗?
我也尝试过使用 substring
+ charindex
手动移动列表,但这会导致大量手动列.
如果列表大小相等:
db<>小提琴演示/p>
相关:STRING_SPLIT 添加返回行号的选项
使用 OPENJSON
获取元素在数组中的确定位置:
db<>fiddle 演示 2
编辑 2:
不同尺寸的处理清单:
db<>fiddle 演示 3
using mssql, if i have data such as:
im trying to create rows of results that join each of those list columns together, such as
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:
db<>fiddle demo
Related: STRING_SPLIT Add Option to Return Row Number
EDIT:
Using OPENJSON
to get deterministic position of element in array:
db<>fiddle demo 2
EDIT 2:
Handling list of different sizes:
db<>fiddle demo 3
这篇关于将在 SQL 中由一个字符拆分的多个列连接在一起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!