求一函数:把行变列?
select 1, 'A ', 'B ', 'C '
union all
select 2, 'AA ', 'BB ', 'CC '
--写函数 转换的结果为
select 1, 'A ' union all
select 1, 'B ' union all
select 1, 'C ' union all
select 2, 'AA ' union all
select 2, 'BB ' union all
select 2, 'CC '
[解决办法]
declare @t table
(id int, c1 varchar(100), c2 varchar(100), c3 varchar(100))
insert into @t
select 1, 'a ', 'b ', 'c ' union select 2, 'aa ', 'bb ', 'cc '
select id,c1 from @t union select id,c2 from @t union select id,c3 from @t
/*
1 a
1 b
1 c
2 aa
2 bb
2 cc
*/
[解决办法]
select id,c1 from (select id=1,c1= 'A ',c2= 'B ',c3= 'C ' union all select id=2,c1= 'AA ',c2= 'BB ',c3= 'CC ') t union all
select id,c2 from (select id=1,c1= 'A ',c2= 'B ',c3= 'C ' union all select id=2,c1= 'AA ',c2= 'BB ',c3= 'CC ') t union all
select id,c3 from (select id=1,c1= 'A ',c2= 'B ',c3= 'C ' union all select id=2,c1= 'AA ',c2= 'BB ',c3= 'CC ') t
[解决办法]
create table t(id int,val1 char(2),val2 char(2),val3 char(2));
insert into t
select 1, 'A ', 'B ', 'C '
union all
select 2, 'AA ', 'BB ', 'CC '
select * from t;
declare @col varchar(8000),@field varchar(20),@i int,@first varchar(20)
set @col= ' '
set @field= ' '
declare cur_t cursor for select name from syscolumns where object_id( 't ')=id;
open cur_t
fetch cur_t into @field
set @first= 'union select '+@field+ ', '
fetch cur_t into @field
while @@fetch_status=0
begin
set @col=@col+@first+@field+ ' from t '
fetch cur_t into @field
print(@col)
end
set @col=right(@col,len(@col)-5)
exec(@col)
print(@col)
close cur_t
deallocate cur_t
[解决办法]
整理一下
create table t(id int,val1 char(2),val2 char(2),val3 char(2));
insert into t
select 1, 'A ', 'B ', 'C '
union all
select 2, 'AA ', 'BB ', 'CC '
select * from t;
declare @col varchar(8000),@field varchar(100),@first varchar(100)
set @col= ' '
set @field= ' '
declare cur_t cursor for select name from syscolumns where object_id( 't ')=id;
open cur_t
fetch cur_t into @field
set @first= 'union select '+@field+ ', '
fetch cur_t into @field
while @@fetch_status=0
begin
set @col=@col+@first+@field+ ' from t '
fetch cur_t into @field
end
set @col=right(@col,len(@col)-5)
exec(@col)
close cur_t
deallocate cur_t
