如何解析列并向表中添加更多记录? [英] how to parse on column and add more records to a table?

问题描述

我有表 tb1(col1, col2)，col2 是 varchar(50).例如，

I have table tb1(col1, col2), col2 is varchar(50). For instance,

col1    col2
item1   abc
item2   a
item3   ed

我想编写一个存储过程来解析 col2 并创建一个临时表，如下所示:

I want to write a stored procedure to parse col2 and create a temp table like following:

col1    col2
item1   a
item1   b
item1   c
item2   a
item3   e
item3   d

有人可以帮我吗?

推荐答案

如果你知道字符串的最大长度，最简单的方法就是做一个简单的联合:

If you know the maximum length of the string, the easiest way is doing a simple union:

select col1, substring(col2, 1, 1) as col2
from t
where len(col2) >= 1 union all
select col1, substring(col2, 2, 1) as col2
from t
where len(col2) >= 2 union all
select col1, substring(col2, 3, 1) as col2
from t
where len(col2) >= 3 union all

如果长度永远不会太长，您可以执行以下操作来简化查询:

If the length is never too long, you can do something like this to simplify the query:

select col1, substring(col2, nums.seqnum) as col2
from t cross join
     (select row_number() over (order by (select NULL)) as seqnum
      from Infromation_Schema.columns
     ) nums
where len(col2) <= nums.seqnum

或者，您可以在 T-SQL 的 while 循环中执行此操作.

Alternatively, you can do this in a while loop in T-SQL.

这篇关于如何解析列并向表中添加更多记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！