--測試資料
CREATE TABLE Test (ID CHAR(10),Item VARCHAR(50),Fld INT)
INSERT INTO Test VALUES ('A','AA,AAA,dddd',1)
INSERT INTO Test VALUES ('B','BB,BBB,Nipsan,chow',2)
-------------------------
--
SELECT IDENTITY(INT,1,1) N INTO #Seq FROM Northwind..[Orders]SELECT DISTINCT ID,SUBSTRING(Item,N+1,CHARINDEX(',',Item,N+1)-(N+1)) AS Item,Fld
FROM (
SELECT id,REPLACE(RTRIM(','+LTRIM(item))+',',',,',',') AS Item,fld from test
) as Test
JOIN #Seq b
ON SUBSTRING(Item,N,8000) Like ',_%'
ORDER BY IDDROP TABLE Test,#Seq
CREATE TABLE Test (ID CHAR(10),Item VARCHAR(50),Fld INT)
INSERT INTO Test VALUES ('A','AA,AAA,dddd',1)
INSERT INTO Test VALUES ('B','BB,BBB,Nipsan,chow',2)
-------------------------
--
SELECT IDENTITY(INT,1,1) N INTO #Seq FROM Northwind..[Orders]SELECT DISTINCT ID,SUBSTRING(Item,N+1,CHARINDEX(',',Item,N+1)-(N+1)) AS Item,Fld
FROM (
SELECT id,REPLACE(RTRIM(','+LTRIM(item))+',',',,',',') AS Item,fld from test
) as Test
JOIN #Seq b
ON SUBSTRING(Item,N,8000) Like ',_%'
ORDER BY IDDROP TABLE Test,#Seq
一条SQL就搞定了.
union
select field1,right(field2,3) as fd2,field3 from table
order by field1,fd2
SELECT ID,
SUBSTRING(','+Item+',',N+1,CHARINDEX(',',','+Item+',',N+1)-(N+1)) AS Item,
Fld
FROM
test,#Seq b
where SUBSTRING(','+Item+',',N,8000) Like ',_%'
ORDER BY ID
高!!!!!
----还有点小问题,如果field2为VARCHAR类型,Orders少于8000条记录......而字符串很很长的话可能要自已做一个表。N从1~8000
不过我举的只是个例子而已
真正情况是field2里的值是不确定的
可能是null可能是空字符串
可能是一个AA,也可能有若干个
这种情况该怎么处理呢?
select f1,left(f2,2),f3
from table1insert into table2
select f1,RIGHT(LEFT(f2,6),3),f3
from table1