表结构转换，比较难呀，麻烦的行变列，实在没办法了，请教大家了

Question

本帖最后由 wxl2ooo 于 2010-09-22 23:04:02 编辑

Answer 1

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, productID varchar(100), buyNum varchar(100))
insert into #
select 1, '(400,321,23,4)', '(1,2,1,2)' union all
select 2, '(12,33,45)', '(2,2,2)' union all
select 3, '(44,5332,33,45)', '(1,1,1,1)' union all
select 4, '(332,313)', '(2,1)' union all
select 5, '(32)', '(5)'update # set productID = replace(replace(productID, '(', ''), ')', ''), buyNum = replace(replace(buyNum, '(', ''), ')', '')if object_id('tempdb.dbo.#Nums') is not null drop table #Nums
select top 100 n = identity(int,1,1) into #Nums from syscolumnsselect b.n, a.id, productID = substring(a.productID+',', b.n, charindex(',',a.productID+',',b.n+1) - b.n) into #1 from # a, #Nums b where substring(','+a.productID, b.n, 1) = ','
update t set n = (select count(1) from #1 where id = t.id and n <= t.n) from #1 as t
select b.n, a.id, buyNum = substring(a.buyNum+',', b.n, charindex(',',a.buyNum+',',b.n+1) - b.n) into #2 from # a, #Nums b where substring(','+a.buyNum, b.n, 1) = ','
update t set n = (select count(1) from #2 where id = t.id and n <= t.n) from #2 as tselect a.id, a.productID, b.buyNum from #1 as a inner join #2 as b on a.n = b.n and a.id = b.id order by a.id, a.n/*
id       productID buyNum
-------- --------- --------
1        400       1
1        321       2
1        23        1
1        4         2
2        12        2
2        33        2
2        45        2
3        44        1
3        5332      1
3        33        1
3        45        1
4        332       2
4        313       1
5        32        5
*/--> 删除测试
drop table #, #Nums, #1, #2

Answer 2

if object_id('tb')>0
drop table tb
create table tb
(
id int,
productid varchar(40),
buynum varchar(40)
)insert into tb
select 1, '(400,321,23,4)', '(1,2,1,2)'
union all
select 2, '(12,33,45)', '(2,2,2)'
union all
select 3, '(44,5332,33,45)', '(1,1,1,1)'
union all
select 4, '(332,313)', '(2,1)'
union all
select 5, '(32)', '(5)';with cte as
(
select id,substring(productid,2,len(productid)-2) as productid,substring(buynum,2,len(buynum)-2) as buynum
from tb
)
SELECT distinct A.id, B.productid,C.buynum
FROM(
    SELECT id, 
productid = CONVERT(xml,'<root><v>' + REPLACE(productid, ',', '</v><v>') + '</v></root>'),
buynum = CONVERT(xml,'<root><v>' + REPLACE(buynum, ',', '</v><v>') + '</v></root>')  FROM cte
)A
OUTER APPLY(
    SELECT productid = N.v.value('.', 'varchar(100)') FROM A.productid.nodes('/root/v') N(v)
)B
outer apply(
select buynum = N.v.value('.','varchar(100)') from A.buynum.nodes('/root/v') N(v)
)C 
order by A.id
结果
1 23 1
1 400 2
1 400 1
1 23 2
1 4 1
1 4 2
1 321 2
1 321 1
2 33 2
2 12 2
2 45 2
3 33 1
3 5332 1
3 45 1
3 44 1
4 313 2
4 313 1
4 332 1
4 332 2
5 32 5

Answer 3

好像直接用select语句写不出来。
#1已经搞定了。
就用他的吧！

Answer 4

create table #test(id int,productID  varchar(50),buyNum varchar(50))
insert #test select 1 ,'400,321,23,4','1,2,1,2'
insert #test select 2 ,'12,33,45','2,2,2'
insert #test select 3 ,'44,5332,33,45','1,1,1,1'
insert #test select 4 ,'332,313','2,1'
insert #test select 5 ,'32','5'
select id
,productID_xml.value('(/row[sql:column("number")]/text())[1]','varchar(10)')  as productID 
,buyNum_xml.value('(/row[sql:column("number")]/text())[1]','varchar(10)')   as buyNum
from #test a
cross apply (select cast('<row>'+replace(productID,',','</row><row>')+'</row>' as XML) as productID_xml,
 cast('<row>'+replace(buyNum,',','</row><row>')+'</row>' as XML) as buyNum_xml) b
cross apply(
select number from master..spt_values where type='p' and number between  1 and productID_xml.value('count(/row)','INT')) c
id          productID  buyNum
----------- ---------- ----------
1           400        1
1           321        2
1           23         1
1           4          2
2           12         2
2           33         2
2           45         2
3           44         1
3           5332       1
3           33         1
3           45         1
4           332        2
4           313        1
5           32         5(14 行受影响)

Answer 5

或者
 
select id,
cast('<row>'+replace(productID,',','</row><row>')+'</row>' as XML).value('(/row[sql:column("number")]/text())[1]','int') as productID,
cast('<row>'+replace(buyNum,',','</row><row>')+'</row>' as XML).value('(/row[sql:column("number")]/text())[1]','int') as buyNum
from #test a
cross apply(
select number from master..spt_values where type='p' and number between  1 and LEN(productID)-LEN(replace(productID,',',''))+1) cid          productID   buyNum
----------- ----------- -----------
1           400         1
1           321         2
1           23          1
1           4           2
2           12          2
2           33          2
2           45          2
3           44          1
3           5332        1
3           33          1
3           45          1
4           332         2
4           313         1
5           32          5(14 行受影响)

Answer 6

东升哥 这是sqlserver下执行的吗，我用的是sql2000，怎么不能执行呢

Answer 7

方法还是很多的sql 2000、和sql server 2005方法还是有差别的。

Answer 8

sql2000下用select 如何直接解决呢

Answer 9

SQL2000环境下处理问题就是麻烦，写的代码量是SQL2005的两倍还多！SQL2000下的语句，供参考...use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
productID char(17),
buyNum char(12)
)
go
--插入测试数据
insert into tb select 1,'（400,321,23,4）','（1,2,1,2）'
union all select 2,'（12,33,45）','（2,2,2）'
union all select 3,'（44,5332,33,45）','（1,1,1,1）'
union all select 4,'（332,313）','（2,1）'
union all select 5,'（32）','（5）'
go
--代码实现select a.id,productID=substring(a.productID+',',a.number,charindex(',',a.productID+',',a.number)-a.number)
,buyNum=substring(b.buyNum+',',b.number,charindex(',',b.buyNum+',',b.number)-b.number)
from (
select *,idd=(select count(*) from (select * from (
select id,productID=replace(replace(productID,'）',''),'（','')
,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
)a
inner join (
select number from master..spt_values where type='p' and number!=0
)b
on len(a.productID)+1>=b.number and substring(','+a.productID,b.number,1)=',' 
)a where id=t.id and number<t.number)+1
from (
select * from (
select id,productID=replace(replace(productID,'）',''),'（','')
,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
)a
inner join (
select number from master..spt_values where type='p' and number!=0
)b
on len(a.productID)+1>=b.number and substring(','+a.productID,b.number,1)=','
)t
)a
inner join (
select *,idd=(select count(*) from (select * from (
select id,productID=replace(replace(productID,'）',''),'（','')
,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
)a
inner join (
select number from master..spt_values where type='p' and number!=0 )b
on len(a.buyNum)+1>=b.number and substring(','+a.buyNum,b.number,1)=','
)a where id=t.id and number<t.number)+1
from (
select * from (
select id,productID=replace(replace(productID,'）',''),'（','')
,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
)a
inner join (
select number from master..spt_values where type='p' and number!=0 )b
on len(a.buyNum)+1>=b.number and substring(','+a.buyNum,b.number,1)=','
)t
)b
on a.id=b.id and a.idd=b.idd/*测试结果id productID buyNum
---------------------
1 400 1
1 321 2
1 23 1
1 4 2
2 12 2
2 33 2
2 45 2
3 44 1
3 5332 1
3 33 1
3 45 1
4 332 2
4 313 1
5 32 5(14 行受影响)
*/