求一函数:把行变列?

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