问题:
I want to insert my split string into my table as you can see :
create table #Organization
(
organizationId bigint,
provienceId bigint,
CityId bigint,...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I want to insert my split string into my table as you can see :
create table #Organization
(
organizationId bigint,
provienceId bigint,
CityId bigint,
TownId bigint
)
Insert Into #Organization ( organizationId)
select p.value from string_split('1,2,3', ',') p
Insert Into #Organization ( provienceId)
select p.value from string_split('1,2,3', ',') p
Insert Into #Organization ( CityId)
select p.value from string_split('1,2,3', ',') p
Insert Into #Organization ( TownId)
select p.value from string_split('1,2,3', ',') p
My expected result is something like this :
1 1 1 1
2 2 2 2
3 3 3 3
But it returns this :
回答1:
Perhaps I'm reading into your question, but I suspect you want to split your string into columns
Example
Declare @YourTable table (SomeColName varchar(max))
Insert Into @YourTable values
('1,2,3')
,('A,B,C')
,('Dog,Cat,Pony')
Select B.*
From @YourTable A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
From (Select Cast('<x>' + replace((Select replace(SomeColName ,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
Returns
Pos1 Pos2 Pos3
1 2 3
A B C
Dog Cat Pony
回答2:
Insert is inserting three rows, one for each value. If you want to combine them into columns, you need to pivot them or (as I prefer) use aggregation.
You also have a table with four columns but only seem to be inserting three, so I suspect you want:
create table #Organization (
organizationId int identity(1, 1) primary key,
provinceId int,
CityId int,
TownId int
);
(I don't see a need for bigint
for this example.)
Obviously, the simplest solution is not to use strings:
Insert Into #Organization (provinceId, CityId, TownId)
values (1, 2, 3);
But if you are using strings, you can try:
Insert Into #Organization (provinceId, CityId, TownId)
select provinceId, CityId, TownId
from (values('1,2,3')) v(str) cross apply
(select max(case when seqnum = 1 then p.value end) as provinceid,
max(case when seqnum = 2 then p.value end) as cityid,
max(case when seqnum = 3 then p.value end) as townid
from (select p.*,
row_number() over (order by charindex(',' + p.value + ',', ',' + v.str + ',')) as seqnum
from string_split(v.str, ',') p
) p
) s;
Note that there is an issue with using string_split()
for this purpose, because it does not "remember" the position of the substring in the original string. This attempts to get around this problem by using charindex()
. In your case, this will work, because the values are all numbers.
Here is a db<>fiddle.
回答3:
I would use pivot to transform the columns into rows. Like:
INSERT INTO #organization
(provinceid,
cityid,
townid)
SELECT *
FROM (SELECT p.value,
RowN = Row_number()
OVER (
ORDER BY (SELECT NULL))
FROM String_split('1,2,3', ',') p) a
PIVOT (Max(a.value)
FOR rown IN ([1],
[2],
[3])) b