英文:
Parse String To Multiple Columns
问题
以下是翻译好的部分：
我有一个格式为"32.49|33|30|100|20.5|21|18|100|33.39|33|30|100|1.4"的字段。我尝试将其拆分为13个不同的列。这个查询在某种程度上可以工作，但当我尝试在Power BI中运行它时，“Cross Apply”连接失败，以及我尝试将数据旋转到列的任何尝试都失败。
我希望找到一个可以在Power BI中很好运行并将分隔字段拆分为列的解决方案。非常感谢任何帮助！
希望这对您有所帮助。如果您有任何其他问题，请随时提出。
英文:
I have a field formatted as "32.49|33|30|100|20.5|21|18|100|33.39|33|30|100|1.4". I am trying to split it out as 13 different columns. This query works somewhat, but when I try to run it in Power BI, the "Cross Apply" join fails, as well as any attempt I have made to pivot the data to columns.
SELECT
DataMessage.MessageDate
, DataMessage.Data
, Sensor.SensorID
, CSNet.CSNetID
, Sensor.SensorName
, Application.ApplicationName
, value
FROM
[corp--monnit1\monnitsql].[Enterprise1].[dbo].[DataMessage] LEFT OUTER JOIN
[corp--monnit1\monnitsql].[Enterprise1].[dbo].[Sensor] ON DataMessage.SensorID = Sensor.SensorID RIGHT OUTER JOIN
[corp--monnit1\monnitsql].[Enterprise1].[dbo].[CSNet] ON Sensor.CSNetID = CSNet.CSNetID LEFT OUTER JOIN
[corp--monnit1\monnitsql].[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'
I am hoping to find a solution that will work well with Power BI and will split the delimited field into columns. Any help is greatly appreciated!
答案1
得分: 1
请尝试以下基于JSON的解决方案。它适用于SQL Server 2016及更高版本。
SQL
-- 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);
Output
| ID | Col1 | Col2 | Col3 | Col4 | Col5 |
|---|---|---|---|---|---|
| 1 | 32.49 | 33 | 30 | 100 | 20.5 |
在实际查询中，请进行以下修改：
-
替换
CROSS APPLY
STRING_SPLIT(DataMessage.Data,'|')为以下内容：
CROSS APPLY (VALUES ('["' + REPLACE(STRING_ESCAPE(DataMessage.Data,'json'),'|','","') + '"]')) AS t1(j)
-
添加到
SELECT子句中：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]')
英文:
Please try the following JSON based solution. It will work starting from SQL Server 2016 onwards.
SQL
-- 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);
Output
| ID | Col1 | Col2 | Col3 | Col4 | Col5 |
|---|---|---|---|---|---|
| 1 | 32.49 | 33 | 30 | 100 | 20.5 |
In your real life query please make the following modification:
-
Replace
CROSS APPLY
STRING_SPLIT(DataMessage.Data,'|')
with the following:
CROSS APPLY (VALUES ('["' + REPLACE(STRING_ESCAPE(DataMessage.Data,'json'),'|','","') + '"]')) AS t1(j)
-
Add to the
SELECTclause: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]')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论