在每个逗号分隔后将数字字符串列拆分为多列 [英] Split the numeric string column in to multiple columns after each comma separated

问题描述

我需要将其转换为三列，长度不是静态的，因为以上数字是基于计算得出的.

I need to convert this in to three columns, length is not static since above numbers are coming based on calculation.

推荐答案

在 SQL Server 2016 中你可以使用 STRING_SPLIT 来分割字符串(一旦你删除了周围的 (和 )) 然后 PIVOT 结果列:

In SQL Server 2016 you can use STRING_SPLIT to split the string (once you remove the surrounding ( and )) and then PIVOT that result to columns:

WITH CTE AS (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM STRING_SPLIT(REPLACE(REPLACE('(0.0000, 3546.0000, 253422.000)', '(', ''), ')', ''), ',')
)
SELECT [1] AS Val1,
       [2] AS Val2,
       [3] AS Val3
FROM CTE
PIVOT (
  MAX(value)
  FOR rn IN ([1], [2], [3])
) p

输出:

Val1        Val2        Val3
0.0000      3546.0000   253422.000

dbfiddle 演示

要对表中的值执行此操作，您只需将 CROSS APPLY STRING_SPLIT 插入 CTE 内的表.例如，如果该列在名为 data 的表中名为 exclusion:

To do this with values in a table, you just need to CROSS APPLY the STRING_SPLIT to the table inside the CTE. For example, if the column is called exclusion in a table called data:

WITH CTE AS (
  SELECT exclusion, value, ROW_NUMBER() OVER (PARTITION BY exclusion ORDER BY (SELECT NULL)) AS rn
  FROM data
  CROSS APPLY STRING_SPLIT(exclusion, ',')
)
SELECT exclusion,
       [1] AS Val1,
       [2] AS Val2,
       [3] AS Val3
FROM CTE
PIVOT (
  MAX(value)
  FOR rn IN ([1], [2], [3])
) p

dbfiddle 演示

这篇关于在每个逗号分隔后将数字字符串列拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！