在SQL或Python中，如何根据分隔符分隔数据？

我的输入数据如下：

Column1       Column2                                                   Column3
Silver        Bridge;#400;#Pool;#410;#Garden;#427;#Lawn;#591;#Shed;#1337    Aaron
Diamond       Meadow;#492                                                   Mike
Gold          Gas;#360;#Electricity;#1991                                   Judy

我需要按如下方式获取输出：

Column1      Column2      NewColumn     Column3
Silver       Bridge        400          Aaron
Silver       Pool          410          Aaron
Silver       Garden        427          Aaron
Silver       Lawn          591          Aaron
Silver       Shed          1337         Aaron
Diamond      Meadow        492          Mike
Gold         Gas           360          Judy
Gold         Electricity   1991         Judy

注意：此处用于分隔数据的分隔符是‘；#’。尝试在第一个分隔符(；#)出现后创建新列，并在第二个分隔符(；#)出现后创建一行，应继续为数据单元格创建新列。

解决方案可以使用SQL或Python，但最好是使用SQL。 我尝试了一些方法，但在输出中，我可以按列或按行分隔，但在这里，我们需要交替按列和行分隔。我认为可以通过运行循环来完成，但到目前为止都不起作用。

---

解决方案

SQL Server中的一个选项是在交叉应用中使用一点JSON和条件聚合

示例或dbFiddle

Select A.Column1 
      ,B.Column2
      ,B.NewColumn
      ,A.Column3
 From  YourTable A
 Cross Apply (
                Select Column2   = max(case when [Key] % 2 = 0 then Value end)
                      ,NewColumn = max(case when [Key] % 2 = 1 then Value end)
                 From  OpenJSON( '["'+replace(string_escape([Column2],'json'),';#','","')+'"]' )
                 Group By [Key] / 2
             ) B

结果