Table 1 a total of two columns, rows variable:
A B
C A
B C D
D Table 2 A total of two columns, rows uncertain
01 A
02 A, B, D
03 B, C
04 C, D
If you generate table 3 (N = null)
01 A A N N N N
02 A A B D B B C D
03 B C N N
04 C C D D N N
Table 1 and Table 2,
韩国国内首次确认了2例感染“超级细菌”的病例, find out how the data is equal to the relevant contact Table 3, Table 2, the second column is the character array.
best not to use loops and cursors, the best SQL can be used to directly solve.
create table tb1 (c1 varchar (10), c2 varchar (10))
insert into tb1 values ('A', 'A')
insert into tb1 values ('B', 'B')
insert into tb1 values ('C', 'c')
insert into tb1 values ('D',
herve leger dress hire, 'd')
create table tb2 (c1 varchar (10), c2 varchar (10))
insert into tb2 values ('01 ',' A ')
insert into tb2 values ('02', 'A, B, D')
insert into tb2 values (' 03 ',' B, C ')
insert into tb2 values ('04', 'C, D')
go
select m.c1,
; m.c21,
isnull ((select c2 + '' from tb1 n where n.c1 = m.c21), 'N' ) +
isnull (m.c22 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c22), 'N') +
isnull (m.c23 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c23), 'N') +
isnull (m.c24 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c24),
nike shoes casual, 'N') c3
from
(
select c1,
; parsename (replace (reverse (c2), ',','.'), 1) c21,
parsename (replace (reverse (c2), ' , ','.'), 2) c22,
parsename (replace (reverse (c2),', ','.'), 3) c23,
parsename (replace (reverse (c2), ',','.'), 4) c24
from tb2
) m
drop table tb1, tb2
from similar problems -
/ * description of the problem
tba
ID classid name
1 ; 1,
sale on timberland boots,2,3 suit
2 2,3 tunic
3 1 , 3 pants
tbb
id classname
1 dress shirt
2 I have 3 pants
result is
id classname ; name
1 clothes, jacket, pants suit
2 jacket, pants ; tunic
3 clothes, pants Name pants
* /
------------------------------------- ----------------
- sql server 2000 in a written
create table tba (ID int, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ',' suits ')
insert into tba values (2, '2, 3', 'tunic')
insert into tba values (3, '1, 3 ',' name of pants ')
create table tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1' , 'clothes')
insert into tbb values ('2 ',' Top ')
insert into tbb values ('3', 'trousers')
go
- the first one kind of method, create a function to display
create function f_hb (@ id varchar (10))
returns varchar (1000)
as
begin
declare @ str varchar ( 1000)
set @ str =''
select @ str = @ str +','+[ classname] from tbb where charindex (','+ cast (id as varchar) +',',',
蝴蝶飞不过沧海,'+id +',')> 0
return stuff (@ str,
阴谋：丈夫每日投0.1克亚硝酸盐欲毒死妻子, 1,1,'')
end
go
select id, classid = dbo.f_hb (classid), name from tba
drop function f_hb
/ *
id classid name
----------- ------------- - ---------
1 clothes, jacket, pants suits
2 ; shirt, pants tunic
3 clothes, trousers were pants
(number of rows affected by line 3)
* /
- 2 ways. update
while (exists (select * from tba, tbb where charindex (tbb.id, tba.classid)> 0))
update tba
set classid = replace (classid, tbb.id, tbb.classname )
from tbb
where charindex (tbb.id, tba.classid)> 0
select * from tba
/ *
ID classid name
----------- -------------------- ----------
1 ; clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(number of rows affected by line 3)
* /
drop table tba, tbb
-------------------------------- ----------------------------------------
- sql server 2005 in the first decomposition tba the classid, and then merge classname
create table tba (ID int, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ', 'suits')
insert into tba values (2, '2, 3', 'tunic')
insert into tba values (3, '1, 3 ',' name pants')
create table tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1 ',' clothes')
insert into tbb values ('2 ', 'Top')
insert into tbb values ('3 ',' trousers')
go
SELECT id, classname, name FROM
(
SELECT DISTINCT id, name FROM (select tbc.id, tbc.name, tbb.classname from
(
SELECT A.id, A.name, B.classid FROM (SELECT id, name, [classid] = CONVERT (xml, ' ' + REPLACE ([classid], ',', ' ') + ' ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',
coach baby bags,' varchar (100) ') FROM A. [ ,],[A
OUTER APPLY
(
SELECT [classname] = STUFF (REPLACE (REPLACE ((
SELECT classname FROM (select tbc.id, tbc . name, tbb.classname from
(
SELECT A.id, A.name, B.classid FROM (SELECT id , name, [classid] = CONVERT (xml, ' ' + REPLACE ([classid],
ed hardy shoes for men, ',', ' ') + ' ; ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',' varchar (100) ') FROM A. [classid]. nodes ('/ root / v') N (v)) B
) tbc, tbb where tbc.classid = tbb.id
) N
WHERE id = A.id and name = A.name
FOR XML AUTO), ' ',''), 1, 1,'')
) N
order by id
drop table tba, tbb
/ *
id ; classname name
----------- ---- ---------- ----------
1 clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(3 rows affected)
* /
nike shoes casual SQL problem
Linear Mode
