求救~高手 SQL问题
数据库结构如下：
id type brand
1 1001,1002,1003 5001,5002,5003
3 1001 5005,5004
我想要把所有type 和brand重合的分出来做一条新数据插入到数据库中，但不能有重复 也就是想查找结果如下
id type brand
1 1001 5001
2 1001 5002
3 1001 5003
4 1002 5001
5 1002 5002
6 1002 5003
7 1003 5001
8 1003 5002
9 1003 5003
10 1001 5005
11 1001 5004
请问应该怎么写，或者也可以用ASP把数据读出来 经过处理后在插入数据库，但不知道应该怎么组合。多谢各位！
[解决办法]
分割字符串
declare @AllChar varchar(50)
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int
set @AllChar= 'afdsf,ASDFRE,WR,QWRQW,A,DSF,EW ' ----可以传入一个字符串
set @lenth=len(@AllChar)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex( ', ',@AllChar)
while( @FirstPoint> 0)
begin
set @FirstChar=substring(@AllChar,0,@FirstPoint)
--select @FirstChar
insert into #Temp_String(Content) values (@FirstChar)
set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth)
set @FirstPoint=charindex( ', ',@AllChar)
end
insert into #Temp_String(Content) values (@AllChar)
select * from #Temp_String
[解决办法]
declare @a table(id int,type varchar(100),brand varchar(100))
insert @a select 1 , '1001,1002,1003 ', '5001,5002,5003 '
union all select 3 , '1001 ', '5005,5004 '
SELECT TOP 100 id = IDENTITY(int, 1, 1)
INTO #a
FROM syscolumns a, syscolumns b
create table #tmp(id int identity(1,1),type varchar(200),brand varchar(100))
insert #tmp(type,brand)
select aa.type type,bb.type brand from
(SELECT a.id,
type=SUBSTRING(a.type, b.id, CHARINDEX( ', ', a.type + ', ', b.id) - b.id)
FROM @a a, #a b
WHERE SUBSTRING( ', ' + a.type, b.id, 1) = ', ')AA
left Join
(
SELECT a.id,
type=SUBSTRING(a.brand, b.id, CHARINDEX( ', ', a.brand + ', ', b.id) - b.id)
FROM @a a, #a b
WHERE SUBSTRING( ', ' + a.brand, b.id, 1) = ', ')
BB
on aa.id=bb.id order by aa.id,type
select * from #tmp
drop table #a
drop table #tmp
