在SQL Server中将分隔的值逗号分隔成多行 [英] Comma separated values in to multiple rows in SQL Server

问题描述

我有一个表，其中包含以下格式的数据

I have a table where it contains data in below format

如何在MS SQL Server中实现这一目标.

How to achieve this in MS SQL Server.

推荐答案

此方法使用 DelimitedSplit8K ，因为需要有关序数位置的信息( STRING_SPLIT 和其他许多拆分器未提供).以下也是伪SQL，因为OP提供了图像，而不是文本数据:

This uses DelimitedSplit8K, as information on the ordinal position is required (something STRING_SPLIT and many other splitters don't supply). The below is Pseudo SQL as well, as the OP has provided images, rather that textual data:

SELECT {YourColumns}
FROM YourTable YT
     CROSS APPLY dbo.DelimitedSplit8K(YT.Qualification,',') DSq
     CROSS APPLY dbo.DelimitedSplit8K(YT.Instituion,',') DSi
WHERE DSq.ItemNumber = DSi.ItemNumber;

但是，正如评论中提到的，这里的真正答案是修复数据模型.

The true answer here, as has been mentioned in the comments, however, is to fix the data model.

另一种方法是使用 OPENJSON .这是我最近才介绍的内容，并且我无权访问SQL Server 2016实例以对此进行测试(尽管我已使用SQL Fiddle对其进行了测试，但出于相同的原因，没有针对提供的图像进行测试)以上).我相信这也应该可以实现您的目标:

An alternative method would be to use OPENJSON. This is something I have only been introduced to recently, and I don't have access to a SQL Server 2016 instance to test this against (I have used SQL Fiddle to test it runs though, but not against the image provided for my same reason above). I beleive this should also achieve your goal though:

SELECT OJq.[value], OJi.[Value]
FROM YourTable YT
     CROSS APPLY (SELECT ca.[Key], ca.[value]
                  FROM OPENJSON('["' + REPLACE(YT.Qualification,',','","') + '"]') ca) OJq
     CROSS APPLY (SELECT ca.[Key], ca.[value]
                  FROM OPENJSON('["' + REPLACE(YT.Instituion,',','","') + '"]') ca) OJi
WHERE OJq.[Key] = OJi.[Key];

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