SQL 多列转多行

Question

t1结构如下：id No  FY1 FY2 FY3 Type
1  1   100 110 120 Target
2  1   101 109 130 Actual我想转换成id No Target Actual FY
1  1  100    101    FY1
2  1  110    109    FY2
3  1  120    130    FY3怎么写？急！！谢谢

Answer 1

先贴个例子：create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2select * from 表
create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'='''+case isnull(@new_fdname,'') when '' then ''
else @new_fdname+'=' end+''''''+name+''''''''
-- ,@s2=@s2+',@'+@i+'=''性别='''''+name+''''''''
,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+['+@fdname+']+'']=''+cast(['+name+'] as varchar) from ['+@tbname+']'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s4=substring(@s4,2,8000)
,@s5=substring(@s5,16,8000)
exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go

Answer 2

select id,No,Target,Fy
from
(
select id,NO,FY1,FY2,FY3,type from t1
)as p
unpivot
(Target for Fy in(FY1,FY2,FY3))
as unpvt但这只有单列啊，，还有一列咋整？

Answer 3

create table #t
(
no int,
fy1 int,
fy2 int,
fy3 int,
type varchar(20)
)insert #t select  1 ,  100 ,110 ,120 ,'Target' 
insert #t select  1  , 101 ,109 ,130 ,'Actual' 
goselect no,
sum(case when type = 'Target' then fy else 0 end) 'Actual',
sum(case when type = 'Actual' then fy else 0 end) 'Target',
case when aa = 1 then 'fy1'
 when aa = 2 then 'fy2'
 when aa = 3 then 'fye' end 'fy' 
from 
(
select no,fy1 'fy',type,1 'aa' from #t
union all
select no,fy2,type,2 from #t
union all
select no,fy3,type,3 from #t
)as tbl
group by no,aa

Answer 4

id列应该是你的行号吧，自行加上就可以了~
sql2005 Row_Number + 行号,2000简单的方法用identity，自己写吧select row_number() over(order by no desc) id,no,
sum(case when type = 'Target' then fy else 0 end) 'Actual',
sum(case when type = 'Actual' then fy else 0 end) 'Target',
case when aa = 1 then 'fy1'
 when aa = 2 then 'fy2'
 when aa = 3 then 'fye' end 'fyi' 
from 
(
select no,fy1 'fy',type,1 'aa' from #t
union all
select no,fy2,type,2 from #t
union all
select no,fy3,type,3 from #t
)as tbl
group by no,aa

Answer 5

--t1结构如下： 
--id No  FY1 FY2 FY3 Type 
--1  1   100 110 120 Target 
--2  1   101 109 130 Actual 
--我想转换成 
--id No Target Actual FY 
--1  1  100    101    FY1 
--2  1  110    109    FY2 
--3  1  120    130    FY3 
--怎么写？急！！谢谢if object_id('tb') is not null
   drop table tb
go
create table tb(id int,No int,FY1 int,FY2 int,FY3 int,Type varchar(10))
insert into tb
select 1,  1,   100, 110, 120, 'Target' union all 
select 2,  1,   101, 109, 130, 'Actual'---下面是实现部分
create table #1 (id int identity(1,1),No int,Target int,Actual int,FY varchar(10))
declare @colName varchar(100)
declare cur cursor for select name from sys.syscolumns where id=object_id('tb') and name <>'id' and name<> 'No'and name<>'type'
open cur
fetch next from cur into @colName
while(@@fetch_status=0)
   begin
      declare @sql varchar(8000)
      set @sql='select No,'
      select @sql=@sql+'max(case when Type='''+a.Type+''' then '+@colName+' else 0 end) as '+a.Type+' ,'
      from (select distinct Type from tb) a
      set @sql=substring(@sql,1,len(@sql))+' '''+@colName+''' as FY from tb group by No'
      print @sql
      insert into #1 exec(@sql)
      fetch next from cur into @colName
   end
close cur
deallocate cur
select * from #1
drop table #1--接分了

Answer 6

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,No int,FY1 int,FY2 int,FY3 int,Type varchar(6))
insert into #T
select 1,1,100,110,120,'Target' union all
select 2,1,101,109,130,'Actual'
;
with
T1 as
(
select Type,ltrim(FY1)+','+ltrim(FY2)+','+ltrim(FY3) FY from #T
),
T2 (id,Type,P1,P2) as
(
select 1,Type,charindex(',',','+FY),charindex(',',FY+',')+1 from T1
union all
select b.id+1,a.Type,b.P2,charindex(',',FY+',',b.P2)+1 from T1 a join T2 b on a.Type=b.Type where charindex(',',FY+',',b.P2)>0
),
T3 as
(
select b.id,a.Type,FY=substring(a.FY+',',b.P1,b.P2 - b.P1 - 1) from T1 a join T2 b on a.Type=b.Type
)
select a.id,No=1,Target=a.FY,Actual=b.FY,FY='FY'+ltrim(a.id) from T3 a join T3 b on a.id=b.id where a.Type='Target' and b.Type='Actual'
/*
id          No          Target      Actual      FY
----------- ----------- ----------- ----------- -----------
1           1           100         101         FY1
2           1           110         109         FY2
3           1           120         130         FY3
*/

Answer 7

谢谢各位，结贴了。
viva369的代码 简单，有效。