table1
====================================
c_b c_c1 c_c2
--------------------------------
A a<>k1 a<>k3
B b<>k1 c<>k1
A a<>k1 e<>k2
B d<>k3 d<>k2
=====================================
table_2
c_A c_B c_1 c_2
---------- ---------- ---------- ----------
k1 A a
k1 B b c
k2 A e
k2 B d
k3 A a
k3 B d
如何由table1生成table2???
实现
if object_id('shiyan') is not null
drop table shiyan
go
create table shiyan(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert into shiyan select 'A','a<>k1','a<>k3'
insert into shiyan select 'B','b<>k1','c<>k1'
insert into shiyan select 'A','a<>k1','e<>k2'
insert into shiyan select 'B','d<>k3','d<>k2'
--以上是生成表
declare @s table(col1 varchar(10),col2 varchar(10),col3 varchar(10),id int) --定义表变量,增加id列便于行列转换
declare @i int
set @i=1
while @i<=(select count(1) from shiyan)
begin
set nocount on
insert into @s select col1,right(col2,2),left(col2,1),1 as id from shiyan
insert into @s select col1,right(col3,2),left(col3,1),2 as id from shiyan
set @i=@i+1
end
set rowcount 0
select col2 as c_A,col1 as c_B ,max(case id when 1 then col3 else ' ' end) as c_1,max(case id when 2 then col3 else ' ' end) as c_2 from @s group by col1,col2
结果
c_A c_B c_1 c_2
---------- ---------- ---------- ----------
k1 A a
k1 B b c
k2 A e
k2 B d
k3 A a
k3 B d
我想不到用一句实现的,如果大家能一句实现的话，希望贴出答案
[此贴子已经被作者于2007-2-11 23:18:27编辑过]
