Find a SQL statement

kittast

Put the following table structure
tableA
---------------------------
News id stock id
---------------------------
3456 601988, 600028, HK3988, HK0386
3457 601988, 600028

How to convert to the following structure
tableB
--------------------------
News id stock id
--------------------------
3456 601988
3456 600028
3456 HK3988
3456 HK0386
3457 601988
3457 600028

----------------------------------
Use one or more SQL statements, but do not use cursors because the amount of data is too large

michealhenry

--reference

CREATE TABLE A (id INT, country VARCHAR (100))
INSERT A
SELECT 1, 'China; Japan; South Korea' UNION ALL
SELECT 2, 'USA; Italy; France' UNION ALL
SELECT 3, 'Germany'
SELECT * FROM A

-Create an auxiliary temporary table
SELECT TOP 8000 id = identity (int, 1,1)
INTO # FROM syscolumns a, syscolumns b

 
SELECT
    A.ID,
    COUNTRY = SUBSTRING (A.COUNTRY, B.ID, CHARINDEX (';', A.COUNTRY + ';', B.ID)-B.ID)
FROM A, # B
WHERE SUBSTRING (';' + a.COUNTRY, B.id, 1) = ';'
ORDER BY 1,2
GO


DROP TABLE A, #

id country
----------- --------------------------------------- -------------------------------------------------- -----------
1 China; Japan; South Korea
2 United States; Italy; France
3 Germany

(3 rows affected)

ID COUNTRY
----------- --------------------------------------- -------------------------------------------------- -----------
1 South Korea
1 Japan
1 China
2 France
2 United States
2 Italy
3 Germany

(7 rows affected)

michealhenry

After querying out, insert the data into another table!

kittast

SELECT TOP 8000 id = identity (int, 1,1)
INTO # FROM syscolumns a, syscolumns b

I still can't understand this sentence

fanfan1980

create table A (col1 int, col2 varchar (100))
insert A select 3456, '601988,600028, HK3988, HK0386'
union all select 3457, '601988,600028'

select top 8000 ID = identity (int, 1, 1) into #T from sysobjects, syscolumns


select col1, col2 = cast (substring (A.col2, B.ID, charindex (',', A.col2 + ',', B.ID) -B.ID) as varchar (20))
from A as A, #T as B
where B.ID <= len (A.col2) and charindex (',', ',' + A.col2, B.ID) = B.ID
order by col1

--result
col1 col2
----------- --------------------
3456 601988
3456 600028
3456 HK3988
3456 HK0386
3457 600028
3457 601988

(6 row (s) affected)

fanfan1980

kittast
 
 
   SELECT TOP 8000 id = identity (int, 1,1)
INTO # FROM syscolumns a, syscolumns b

I still can't understand this sentence
  
 
-------------
Generate a temporary table

1510wxs

SELECT TOP 8000 id = identity (int, 1,1) INTO # FROM syscolumns a, syscolumns b
The generated temporary table is used for looping, and this algorithm can be regarded as a special loop.This loop is not achieved by WHILE loop but by associating the ID of the temporary table with the order of the commas in the string cycle.

sinexe

The dumbest way is
CREATE TABLE A (news id varchar (100), stock id VARCHAR (100))
INSERT A
SELECT '3456', '601988,600028, HK3988, HK0386' UNION ALL
SELECT '3457', '601988,600028'

declare @sql varchar (8000), @id varchar (8000)
select @sql = ''
select @id = ',' + stock id, @ sql = @sql + replace (@id, ',', '' 'union all select' '' + news id + '' ',' '')
from A
select @sql = substring (@ sql, 12, len (@sql)) + '' ''
exec (@sql)
DROP TABLE A


(2 rows affected)

            
---- ------
3456 601988
3456 600028
3456 HK3988
3456 HK0386
3457 601988
3457 600028

lancelover

create table A (col1 int, col2 varchar (100))
insert A select 3456, '601988,600028, HK3988, HK0386'
union all select 3457, '601988,600028'


create function separate (@ch varchar (8000))
returns VARCHAR (1000)
as
begin
declare @wz int
declare @sub varchar (1000)
while (charindex (',', @ ch)> 0)
begin
set @ wz = charindex (',', @ ch)
SET @ SUB = substring (@ ch, 1, @ wz-1)
end
return (@sub)
end


select col1, dbo.separate (A.col2)
from A
order by col1

kittast

Upstairs, your process is an endless loop