在python或sql中动态地将列分割成多个

Question

我正在尝试使用T-sql或python将详细信息列拆分为多个。

表是这样的:

酒店:露营地;留言:预订查询 150"text-align:左;"页面:45-discount-y; PageLink: https://xx.xx.net/SS/45-discount-y/|

首先:使用string_split分割部分数据

second:在用string_split分割数据的second部分之后，替换

我们用replace替换'Page Link in'

中的句柄字符:最后使用pivot

dECLARE @cols AS NVARCHAR(MAX),@scols  AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

set @query = '


;with cte as (
select Id,Details,valuesd,[1],replace( [2],''https//'',''https://'') as [2]   from (
select * from (

select Id,Details,value as valuesd
from  T
cross apply(  
select *
from string_split(Details,'';'')
)d

)t 
cross apply (
select RowN=Row_Number() over (Order by (SELECT NULL)), value 
from string_split(replace( t.valuesd,''https://'',''https//''), '':'') 
) d
) src
pivot (max(value) for src.RowN in([1],[2])) p
)
SELECT   T.id,T.Details,Max([Hotel]) as [Hotel],Max([Message]) as Message,Max([Page]) as Page,Max([PageLink]) as PageLink  from 
(
select Id,Details,  valuesd,[1],[2]
from  cte
) x
pivot 
(
max( [2])  for [1] in ([Hotel],[Message],[Page],[PageLink])
) p 
right join T on p.id=T.id
group by T.id,T.Details
'
execute(@query)

您可以使用以下代码

插入基本数据

create table T(id int,Details nvarchar(max))
insert into T
select 15,'Hotel:Campsite;Message:Reservation inquiries' union all
select 150,'Page:45-discount-y;PageLink:https://xx.xx.net/SS/45-discount-y/|' union all
select 13, null