当前位置： 文江博客话题详情

如何通过将字符串从列到列（不使用创建函数）中的SQL中的列来创建视图

发布于 2025-02-09 07:41:40 字数 1514 浏览 1 评论 0原文

我有一个表“产品” 由两个列组成： id，raw

示例：

IDRAW
132描述：芒果；费用：15-30;储蓄：64; ER：E 51; ENR：D 56;符合条件：y $ $描述：oranges;费用：1,000-1,400；储蓄：41; ER：C 75; ENR：D 58;资格：Y
497说明：香蕉；费用：25;储蓄：9; ER：E 52; ENR：D 56;合格：n $ $描述：苹果；费用：4,000-14,000；储蓄：165; ER：E 47; ENR：E 53;符合条件：y $ $描述：梨；费用：800-1,200；储蓄：393; ER：C 73; ENR：D 55;符合条件：Y

为最终视图：

ID说明省略该表需要将其转换D 58 D132符合条件
MANGOES15-3064E51D 56Y
132ORANGES1,000-1,40041C 7558Y
497BANANAS259E 52D 56N
497苹果4,000-14,000165E 47E 53Y
497梨800-1,200393C 73D 55y

我应该通过下一步做到这一点：

  1. 使用 split（） >'$ $'
  2. 使用 split（） by ';'将每个 string划分为行从上一个步骤到分开列并将其命名。

但是我终于使如何做到这一点感到困惑。

谁能提出解决方案或更优雅的方式来意识到这一点？

我认为解决方案是表面上的，但我似乎无法做到。谢谢。

原文

I've got a table "products" which consists of two columns: id, raw

Example:

idraw
132Description: mangoes; Cost: 15-30; Saving: 64; ER: E 51; EnR: D 56; Eligible: Y$ $ Description: oranges; Cost: 1,000-1,400; Saving: 41; ER: C 75; EnR: D 58; Eligible: Y
497Description: bananas; Cost: 25; Saving: 9; ER: E 52; EnR: D 56; Eligible: N$ $ Description: apples; Cost: 4,000-14,000; Saving: 165; ER: E 47; EnR: E 53; Eligible: Y$ $ Description: pears; Cost: 800-1,200; Saving: 393; ER: C 73; EnR: D 55; Eligible: Y

This table needs to be converted to the final view like this:

idDescriptionCostSavingEREnREligible
132mangoes15-3064E 51D 56Y
132oranges1,000-1,40041C 75D 58Y
497bananas259E 52D 56N
497apples4,000-14,000165E 47E 53Y
497pears800-1,200393C 73D 55Y

I supposed to do that by the next steps:

  1. using SPLIT() by '$ $ ' to split each 'raw' string to rows
  2. using SPLIT() by ';' to split each row from the previous step to separate column and name it.

But I finally confused how to do that.

Could anyone suggest the solution or more elegant way to realize that?

I think the solution is on the surface, but I can't seem to get to it. Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

表情可笑 2025-02-16 07:41:40

使用'$ $'使用split（）将每个'raw'字符串分开为行

拆分不分为行。它分成数组成员。要拆分为行，请使用横向连接到split_to_table表函数：

select ID, split(VALUE, ';')
from t1, table(split_to_table(raw, '$ 

您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：


with X as
(
select ID, parse_json('{"' || replace(replace(replace(trim(VALUE), ':', '":"'), ';','","'), '" ', '"') || '"}') as V
from t1, table(split_to_table(raw, '$ 

但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。


create or replace function stk_array_to_object(A array)
returns object
language javascript
strict immutable
as
$
    var kv;
    out = {};
    for(let i = 0; i < A.length; i++) {
        kv = A[i].split(":");
        kv[0] = kv[0].trim();
        out[kv[0]] = kv[1].trim();
    }
    return out;
$;



然后，您可以获取这样的对象：


select ID, stk_array_to_object(split(VALUE, ';')) as V
from t1, table(split_to_table(raw, '$ 

从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：


with X as
(
select ID, stk_array_to_object(split(VALUE, ';')) as V
from t1, table(split_to_table(raw, '$ 
));



您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：




但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。




然后，您可以获取这样的对象：




从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：



))
)
select   ID
        ,v:Description::string as "Description"
        ,v:Cost::string        as "Cost"
        ,v:Saving::string      as "Saving"
        ,v:ER::string          as "ER"
        ,v:EnR::string         as "ENR"
        ,v:Eligible::string    as "Eligible"
from X
;



但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。




然后，您可以获取这样的对象：




从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：



));



您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：




但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。




然后，您可以获取这样的对象：




从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：



)) 

从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X; ));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
))
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
));
您可以使用字符串操纵来形成，解析和对象，然后从中拉出列：
但是；尽管标题指定“不使用创建功能”，但它将使情况更加清洁。我写了一个Java UDF，该Java UDF采用了一系列令牌字符串（由结肠隔开），并将其转换为对象中的键和值。
然后，您可以获取这样的对象：
从那里，您可以做类似CTE的事情来投射列的密钥并隐藏对象：
using SPLIT() by '$ $ ' to split each 'raw' string to rows
Split doesn't split into rows. It splits into array members. To split to rows, use a lateral join to the split_to_table table function:
select ID, split(VALUE, ';')
from t1, table(split_to_table(raw, '$ 

You could use string manipulation to form and parse and object, and then pull the columns from it like this:


with X as
(
select ID, parse_json('{"' || replace(replace(replace(trim(VALUE), ':', '":"'), ';','","'), '" ', '"') || '"}') as V
from t1, table(split_to_table(raw, '$ 

However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.


create or replace function stk_array_to_object(A array)
returns object
language javascript
strict immutable
as
$
    var kv;
    out = {};
    for(let i = 0; i < A.length; i++) {
        kv = A[i].split(":");
        kv[0] = kv[0].trim();
        out[kv[0]] = kv[1].trim();
    }
    return out;
$;



You can then get an object like this:


select ID, stk_array_to_object(split(VALUE, ';')) as V
from t1, table(split_to_table(raw, '$ 

From there, you could do something like a CTE to project the keys to columns and hide the object:


with X as
(
select ID, stk_array_to_object(split(VALUE, ';')) as V
from t1, table(split_to_table(raw, '$ 
));



You could use string manipulation to form and parse and object, and then pull the columns from it like this:




However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.




You can then get an object like this:




From there, you could do something like a CTE to project the keys to columns and hide the object:



))
)
select   ID
        ,v:Description::string as "Description"
        ,v:Cost::string        as "Cost"
        ,v:Saving::string      as "Saving"
        ,v:ER::string          as "ER"
        ,v:EnR::string         as "ENR"
        ,v:Eligible::string    as "Eligible"
from X
;



However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.




You can then get an object like this:




From there, you could do something like a CTE to project the keys to columns and hide the object:



));



You could use string manipulation to form and parse and object, and then pull the columns from it like this:




However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.




You can then get an object like this:




From there, you could do something like a CTE to project the keys to columns and hide the object:



)) 

From there, you could do something like a CTE to project the keys to columns and hide the object:
));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X; ));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
))
From there, you could do something like a CTE to project the keys to columns and hide the object:
));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
)) ) select ID ,v:Description::string as "Description" ,v:Cost::string as "Cost" ,v:Saving::string as "Saving" ,v:ER::string as "ER" ,v:EnR::string as "ENR" ,v:Eligible::string as "Eligible" from X ;
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
));
You could use string manipulation to form and parse and object, and then pull the columns from it like this:
However; though the title specifies "without using create function", it's going to make the situation cleaner. I wrote a Java UDF that takes an array of tokenized strings (separated by colons) and converts it into keys and values in an object.
You can then get an object like this:
From there, you could do something like a CTE to project the keys to columns and hide the object:
 回复 原文 
Spring初心 2025-02-16 07:41:40
另一种方法 -
第一个CTE是原始数据。
第二个CTE（CTE_1）将每个数据分配为“ $ $”。重要的是要注意的是捕获索引，后来将用于枢轴分组。没有此索引值，因为相同ID的行之间没有任何唯一的唯一。
第三CTE（CTE_2），再次将数据分配为';'并使用split_part将密钥和值分为单独的列。
Final CTE（CTE_3）在键上执行Pivot以获取最终所需的输出。
with cte(id, raw) as
(select * from values
(132,'Description: mangoes; Cost: 15-30; Saving: 64; ER: E 51; EnR: D 56; Eligible: Y
$ $ Description: oranges; Cost: 1,000-1,400; Saving: 41; ER: C 75; EnR: D 58; Eligible: Y'),
(497,'Description: bananas; Cost: 25; Saving: 9; ER: E 52; EnR: D 56; Eligible: N
$ $ Description: apples; Cost: 4,000-14,000; Saving: 165; ER: E 47; EnR: E 53; Eligible: Y
$ $ Description: pears; Cost: 800-1,200; Saving: 393; ER: C 73; EnR: D 55; Eligible: Y')
), cte_1 as (
select id,c.index,c.value from cte, lateral flatten (input=>split(raw,'$ 





 id
描述
 '
 '
 '
 '
 '






成本
'
'
​
​
​
​




​
​
​
​
​
​
​




​
​
​
​
​
 ​55
 Y




 497
香蕉
25
 9
 E 52
 D 56
 N




 497
苹果
4,000-14,000
 165
 E 47 E 47
 E 53
 y






 Notes-在数据段（主CTE）中，有运输回报（左右$ $ $），以避免过多的右手滚动。

如果有人复制示例进行测试运行，可能需要调整该调整。

)) c
), cte_2 as (
select c.id, c.index, trim(split_part(f.value,':',1),' ') key,
trim(split_part(f.value,':',2),' ') val
from cte_1 c,
lateral flatten (input=>split(c.value,';')) f
), cte_3 as (
select * from
(select id, index,key, val from cte_2) tab
pivot (max(val) for key in ('Description','Cost','Saving','ER','EnR','Eligible'))
)
select id,"'Description'","'Cost'","'Saving'","'ER'","'EnR'","'Eligible'" 
from cte_3;

id描述'''''
成本''​​​​
​​​​​​​
​​​​​​55Y
497香蕉259E 52D 56N
497苹果4,000-14,000165E 47 E 47E 53y
Notes-在数据段（主CTE）中，有运输回报（左右$ $ $），以避免过多的右手滚动。
如果有人复制示例进行测试运行，可能需要调整该调整。
Another approach -
First CTE is raw data.
Second CTE (cte_1) splits each data at '$ $'. Important to note here is to capture index, which will be later used in pivot grouping. Without this index value as there is nothing unique across rows of same ID.
Third CTE (cte_2), again splits data at ';' and separates the key and value into separate columns, using split_part.
Final CTE (cte_3) does the pivot on key to get the final desired output.
with cte(id, raw) as
(select * from values
(132,'Description: mangoes; Cost: 15-30; Saving: 64; ER: E 51; EnR: D 56; Eligible: Y
$ $ Description: oranges; Cost: 1,000-1,400; Saving: 41; ER: C 75; EnR: D 58; Eligible: Y'),
(497,'Description: bananas; Cost: 25; Saving: 9; ER: E 52; EnR: D 56; Eligible: N
$ $ Description: apples; Cost: 4,000-14,000; Saving: 165; ER: E 47; EnR: E 53; Eligible: Y
$ $ Description: pears; Cost: 800-1,200; Saving: 393; ER: C 73; EnR: D 55; Eligible: Y')
), cte_1 as (
select id,c.index,c.value from cte, lateral flatten (input=>split(raw,'$ 





ID
'Description'
'Cost'
'Saving'
'ER'
'EnR'
'Eligible'






132
mangoes
15-30
64
E 51
D 56
Y




132
oranges
1,000-1,400
41
C 75
D 58
Y




497
pears
800-1,200
393
C 73
D 55
Y




497
bananas
25
9
E 52
D 56
N




497
apples
4,000-14,000
165
E 47
E 53
Y






Note - in the data-section (main cte) there are carriage returns (around $ $) to avoid too much right-scrolling.

Might need to adjust that if someone copy-pastes the example to test run.

)) c
), cte_2 as (
select c.id, c.index, trim(split_part(f.value,':',1),' ') key,
trim(split_part(f.value,':',2),' ') val
from cte_1 c,
lateral flatten (input=>split(c.value,';')) f
), cte_3 as (
select * from
(select id, index,key, val from cte_2) tab
pivot (max(val) for key in ('Description','Cost','Saving','ER','EnR','Eligible'))
)
select id,"'Description'","'Cost'","'Saving'","'ER'","'EnR'","'Eligible'" 
from cte_3;

ID'Description''Cost''Saving''ER''EnR''Eligible'
132mangoes15-3064E 51D 56Y
132oranges1,000-1,40041C 75D 58Y
497pears800-1,200393C 73D 55Y
497bananas259E 52D 56N
497apples4,000-14,000165E 47E 53Y
Note - in the data-section (main cte) there are carriage returns (around $ $) to avoid too much right-scrolling.
Might need to adjust that if someone copy-pastes the example to test run.
 回复 原文 
~没有更多了~
关于作者
 软的没边
暂无简介
 文章
 评论
26 人气
 发私信
相关话题
更多 
热门标签
操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多 
推荐作者
十二
文章 0 评论 0
飞烟轻若梦
文章 0 评论 0
OPleyuhuo
文章 0 评论 0
wxb0109
文章 0 评论 0
旧城空念
文章 0 评论 0
-小熊_
文章 0 评论 0
更多 
友情链接
文江博客
       
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
     
    原文