将字符串解析为多列

Question

我有一个格式化为"32.49|33|30|100|20.5|21|18|100|33.39|33|30|100|1.4"我试着把它分成13列。这个查询在某种程度上起作用，但是当我试图在Power BI中运行它时，"交叉应用"join失败，以及任何试图将数据枢轴到列的尝试。

SELECT 
DataMessage.MessageDate
, DataMessage.Data
, Sensor.SensorID
, CSNet.CSNetID
, Sensor.SensorName
, Application.ApplicationName
, value
FROM 
[corp--monnit1monnitsql].[Enterprise1].[dbo].[DataMessage] LEFT OUTER JOIN
[corp--monnit1monnitsql].[Enterprise1].[dbo].[Sensor] ON DataMessage.SensorID = Sensor.SensorID RIGHT OUTER JOIN
[corp--monnit1monnitsql].[Enterprise1].[dbo].[CSNet] ON Sensor.CSNetID = CSNet.CSNetID LEFT OUTER JOIN
[corp--monnit1monnitsql].[Enterprise1].[dbo].[Application] ON Sensor.ApplicationID = Application.ApplicationID
CROSS APPLY
STRING_SPLIT(DataMessage.Data,'|')
WHERE
CHARINDEX('|',DataMessage.Data)>0
AND Application.ApplicationName ='ThreePhaseCurrentMeter500' 
AND Csnet.CSNetID = 10 
AND DataMessage.MessageDate > '03/29/2023' 
AND DataMessage.Data != '0'

我希望找到一个解决方案，将与Power BI工作得很好，并将分隔字段分割成列。任何帮助都非常感谢!

Answer 1

请尝试以下基于JSON的解决方案。它将从SQL Server 2016开始工作。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT @tbl (tokens) VALUES
('32.49|33|30|100|20.5|21|18|100|33.39|33|30|100|1.4');
-- DDL and sample data population, end
SELECT t.ID
, Col1 = JSON_VALUE(j,'$[0]')
, Col2 = JSON_VALUE(j,'$[1]')
, Col3 = JSON_VALUE(j,'$[2]')
, Col4 = JSON_VALUE(j,'$[3]')
, Col5 = JSON_VALUE(j,'$[4]') 
FROM @tbl AS t
CROSS APPLY (VALUES ('["' + REPLACE(STRING_ESCAPE(tokens,'json'),'|','","') + '"]')) AS t1(j);

<表类>IDCol1Col2Col3Col4Col5tbody><<tr>132.49333010020.5