SQL解析键值字符串 [英] SQL to Parse a Key-Value String

问题描述

我有一个逗号分隔的字符串，如下所示:

I have a comma delimited string like this:

key1=value1,key2=value2,key3=value3,key1=value1.1,key2=value2.1,key3=value3.1

，我想将其解析为如下所示的表:

and I'd like to parse it into a table that looks like this:

Key1       Key2       Key3
==============================
value1     value2     value3
value1.1   value2.1   value3.1

我可以将字符串分成几行:

I'm able to split the string into rows:

ID      Data
================
1       key1=value1
2       key2=value2
3       key3=value3
...

但是我被困在那里，似乎无法找出一种方法来完成其余的工作.感谢您的帮助.

but I get stuck there and can't seem to figure out a way to do the rest. Any help is appreciated.

推荐答案

如果您能够将数据放入每行格式一个键/值对中，那么将完成许多工作.让我称该结果为t.这样的事情可能会帮助您解决其他问题:

If you are able to get your data into the one key/value pair per row format, then much of the work is done. Let me call that result t. Something like this might get you the rest of the way:

select max(case when LEFT(data, 4) = 'key1' then SUBSTRING(data, 6, len(data)) end) as key1,
       MAX(case when LEFT(data, 4) = 'key2' then SUBSTRING(data, 6, len(data)) end) as key2,
       MAX(case when LEFT(data, 4) = 'key2' then SUBSTRING(data, 6, len(data)) end) as key3
from t
group by (id - 1)/3

这假定id是按顺序分配的，如您的示例所示.

This assumes that the id is assigned sequentially, as shown in your example.

这篇关于SQL解析键值字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！