Multiple plans for a SQL statement

sky6228

Existing Form A
ID Name Class
-------------------
12 a 34 || 35 || 36
13 b 34 || 36
14 c 33 || 34
15 d 34 || 35
16 e 34
17 f 34 || 37

Find a SQL statement and hope to get the following query results:
ID Name Class
------------------
12 a 34
12 a 35
12 a 36
13 b 34
13 b 36
14 c 33
14 c 34
15 d 34
15 d 35
16 e 34
17 f 34
18 f 37

It would be better if we could provide multiple solutions

prajna

--Build a function, the following is a reference

CREATE FUNCTION f_splitSTR (
@s varchar (8000),-string to be split
@split varchar (10)-data separator
RETURNS @re TABLE (col varchar (100))
AS
BEGIN
DECLARE @splitlen int
SET @ splitlen = LEN (@split) -1
WHILE CHARINDEX (@ split, @ s)> 0
BEGIN
INSERT @re VALUES (LEFT (@ s, CHARINDEX (@ split, @ s) -1))
SET @ s = STUFF (@ s, 1, CHARINDEX (@ split, @ s) + @ splitlen, '')
END
INSERT @re VALUES (@s)
RETURN
END

fanfan1980

create table T (ID int, Name char (1), Class varchar (50))
insert T select 12, 'a', '34 || 35 || 36 '
union all select 13, 'b', '34 || 36 '
union all select 14, 'c', '33 || 34 '
union all select 15, 'd', '34 || 35 '
union all select 16, 'e', ​​'34'
union all select 17, 'f', '34 || 37 '

declare @sql varchar (8000)
set @ sql = ''
declare @ID int, @Name char (1), @Class varchar (50)
declare T_cursor cursor for
select ID, Name, Class from T
open T_cursor

fetch next from T_cursor into @ID, @Name, @Class
while @@ fetch_status = 0
begin
select @ sql = @ sql + 'select' + rtrim (@ID) + ',' '' + @ Name + '' ','

select @ sql = @ sql + replace (@Class, '||', 'union all select' + rtrim (@ID) + ',' '' + @ Name + '' ',')

select @ sql = @ sql + 'union all'
fetch next from T_cursor into @ID, @Name, @Class
end
select @ sql = left (@sql, len (@sql) -9)
exec (@sql)
close T_cursor
deallocate T_cursor

sky6228

The cursor is fine, though. . . Is there a cursor?

fanfan1980

Because the ID and Name fields are also retained, it may not be possible to use no cursor.

lilily

Create a string split function, and then use cross apply to associate
Quote: / *
 prajna
   
--Build a function, the following is a reference

CREATE FUNCTION f_splitSTR (
@s varchar (8000),-string to be split
@split varchar (10)-data separator
RETURNS @re TABLE (col varchar (100))
AS
BEGIN
DECLARE @splitlen int
SET @ splitlen = LEN (@split) -1
WHILE CHARINDEX (@ split, @ s)> 0
BEGIN
INSERT @re VALUES (LEFT (@ s, CHARINDEX (@ split, @ s) -1))
SET @ s = STUFF (@ s, 1, CHARINDEX (@ split, @ s) + @ splitlen, '')
END
INSERT @re VALUES (@s)
RETURN
END
* /
select * from A
cross apply f_splitSTR (Class, '||')

kxiaper

create table test_1 (id int, name char (10), class varchar (50))
go
insert test_1
select 12, 'a', '34 || 35 || 36 'union all
select 13, 'b', '34 || 36 'union all
select 14, 'c', '33 || 34 'union all
select 15, 'd', '34 || 35 'union all
select 16, 'e', ​​'34' union all
select 17, 'f', '34 || 37 '
go

create table test_2 (id int, name char (10), class varchar (50))
go

select 1
while @@ rowcount> 0
-When the begin ... end statement block is executed, the number of rows affected by the execution is 0, which means that the lessons, score1, and score2 fields of the test_1 table have been cleared, and the loop is complete
begin
insert test_2
select id, name, class
from test_1
where charindex ('|', class) = 0 and class <> ''

insert test_2
select id, name, left (class, charindex ('|', class) -1) as class-take the first class name
from test_1
where charindex ('|', class)> 0

update test_1 set class = ''
where charindex ('|', class) = 0

update test_1 set
class = substring (class, charindex ('|', class) + 2, len (class) -charindex ('|', class))-take all classes except the first class
where charindex ('|', class)> 0
end
go

select * from test_2
order by id, name, class

drop table test_1, test_2

/ *
id name class
12 a 34
12 a 35
12 a 36
13 b 34
13 b 36
14 c 33
14 c 34
15 d 34
15 d 35
16 e 34
17 f 34
17 f 37
* /

flashhw

select * from (select id, name, class = case when len (class)> = 2 then substring (class, 1,2) end
from t where class is not null
union all
select id, name, class = case when len (class)> = 6 then substring (class, 5,2) end
from t where class is not null
union all
select id, name, class = case when len (class)> = 10 then substring (class, 9,2) end
from t where class is not null) tt
where tt.class is not null

kxiaper

-Establish test data
create table test_1 (id int, name char (10), class varchar (50))
go
insert test_1
select 12, 'a', '34 || 35 || 36 'union all
select 13, 'b', '34 || 36 'union all
select 14, 'c', '33 || 34 'union all
select 15, 'd', '34 || 35 'union all
select 16, 'e', ​​'34' union all
select 17, 'f', '34 || 37 '
go

create table test_2 (id int, name char (10), class varchar (50))
go

-Perform the update and insert the results into a temporary table test_2
select 1
while @@ rowcount> 0
-When the begin ... end statement block is executed, the number of rows affected by the execution is 0, which means that the class field of the test_1 table has been cleared and the loop is complete
begin
insert test_2
select id, name, class
from test_1
where charindex ('|', class) = 0 and class <> ''

insert test_2
select id, name, left (class, charindex ('|', class) -1) as class-take the first class name
from test_1
where charindex ('|', class)> 0

update test_1 set class = ''
where charindex ('|', class) = 0

update test_1 set
class = substring (class, charindex ('|', class) + 2, len (class) -charindex ('|', class))-all classes except the first
where charindex ('|', class)> 0
end
go
--View temporary table results
select * from test_2
order by id, name, class

--Delete the test environment
drop table test_1, test_2

--Temporary table results
/ *
id name class
12 a 34
12 a 35
12 a 36
13 b 34
13 b 36
14 c 33
14 c 34
15 d 34
15 d 35
16 e 34
17 f 34
17 f 37
* /

wtyrambo

create table #temp
(
ID numeric,
name varchar (10),
class varchar (20)
)

insert into #temp values ​​(12, 'a', '34 || 35 || 36')
insert into #temp values ​​(13, 'b', '34 || 36')
insert into #temp values ​​(14, 'c', '33 || 34')
insert into #temp values ​​(15, 'd', '34 || 35')
insert into #temp values ​​(16, 'e', ​​'34')
insert into #temp values ​​(17, 'f', '34 || 37')

Declare @ID numeric
Declare @name varchar (10)
Declare @class varchar (20)
Declare @position int

create table #results
(
ID numeric,
name varchar (10),
class varchar (20)
)

Declare temp_cursor Cursor for
   select * from #temp

Open temp_cursor
Fetch next From temp_cursor into @ ID, @ name, @ class
while @@ fetch_status = 0
Begin
  set @ position = charindex ('||', @ class)
  
  if (@ position = 0)
  insert into #results values ​​(@ ID, @ name, @ class)

  while (@position> 0)
  Begin
    insert into #results values ​​(@ ID, @ name, substring (@ class, 1, @ position-1))
    set @ class = substring (@ class, @ position + 2, len (@class))
    set @ position = charindex ('||', @ class)
   
    if (@ position = 0)
    Begin
       insert into #results values ​​(@ ID, @ name, @ class)
       Break
    End
  End
Fetch next From temp_cursor into @ ID, @ name, @ class
End

Close temp_cursor
Deallocate temp_cursor

select * from #results

final result:
12 a 34
12 a 35
12 a 36
13 b 34
13 b 36
14 c 33
14 c 34
15 d 34
15 d 35
16 e 34
17 f 34
17 f 37