MS SQL 2000字段拆分

Question

本帖最后由 wsktdkj 于 2010-11-28 14:38:25 编辑

Answer 1

CREATE TABLE T2(rwdh int,tzsj datetime,tzry nvarchar(20),tbz nvarchar(max))INSERT T2 
SELECT 3210,'2010-11-11','张三','{粉煤灰(II级)=100}{矿粉(S95)=60}{砂2(中砂)=664}{石子1(碎卵石)=560}{水(自来水)=120}{外加剂2(FDN-A)=6.10}{石子3(碎卵石)=600}{水泥2(P.O42.5)=200}'WITH Ailly
AS
(
SELECT rwdh,tzsj=convert(varchar(10),tzsj,120),tzry,
T.c.value('Name[1]', 'varchar(10)') as 材料,
T.c.value('Quantity[1]', 'float') as 数量
FROM(
select rwdh,tzsj,tzry,
doc=cast(
'<tbz>'+replace(replace(replace(replace(tbz,'{','<Item><Name>')
,'(','</Name><Type>'),')=','</Type><Quantity>'),'}','</Quantity></Item>')+'</tbz>'
 as xml)
from T2
) TB cross apply doc.nodes('/tbz/Item') T(c)
),CTE
AS
(
SELECT rwdh,tzsj,tzry,数量,
材料=(CASE patindex('%[0-9]%',材料) when 0  then 材料 
ELSE SUBSTRING(材料,1,patindex('%[0-9]%',材料)-1) 
  END)
FROM Ailly
)SELECT *
FROM CTE PIVOT 
(
SUM(数量)
FOR 材料 IN ([粉煤灰], [矿粉], [砂], [石子], [水],[水泥],[外加剂])
) Trwdh   tzsj tzry    粉煤灰     矿粉     砂      石子      水     水泥     外加剂
3210   2010-11-11 张三    100        60       664     1160      120    200      6.1(1 行受影响)

Answer 2

CTE,APPLY,PIVOT是SQL SERVER 2005的特性。

Answer 3

/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from tb a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1.使用xml完成
SELECT A.id, B.value FROM
(
  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
) B--方法2.使用CTE完成
;with tt as 
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 4

MARK下，还没有写过SQL SERVER的行列转换