字段行转列的SQL

Question

行列互转
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 1

create table tb(Field1 varchar(10),Field2 varchar(10))
insert into tb values('aa1' ,'bb1') 
insert into tb values('aa2' ,'bb2') 
insert into tb values('aa3' ,'bb3') 
insert into tb values('aa4' ,'bb4') 
insert into tb values('aa5' ,'bb5') 
insert into tb values('aa6' ,'bb6')
go--静态SQL。
select 
  max(case px when 1 then field1 else '' end) f1_1,
  max(case px when 1 then field2 else '' end) f2_1,
  max(case px when 2 then field1 else '' end) f1_2,
  max(case px when 2 then field2 else '' end) f2_2,
  max(case px when 3 then field1 else '' end) f1_3,
  max(case px when 3 then field2 else '' end) f2_3,
  max(case px when 4 then field1 else '' end) f1_4,
  max(case px when 4 then field2 else '' end) f2_4,
  max(case px when 5 then field1 else '' end) f1_5,
  max(case px when 5 then field2 else '' end) f2_5,
  max(case px when 6 then field1 else '' end) f1_6,
  max(case px when 6 then field2 else '' end) f2_6
from
(
  select *,id=1, px = (select count(1) from tb where field1 < t.field1 or (field1 = t.field1 and field2 < t.field2)) + 1 from tb t
) m
group by iddrop table tb/*
f1_1       f2_1       f1_2       f2_2       f1_3       f2_3       f1_4       f2_4       f1_5       f2_5       f1_6       f2_6       
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
aa1        bb1        aa2        bb2        aa3        bb3        aa4        bb4        aa5        bb5        aa6        bb6（所影响的行数为 1 行）
*/

Answer 2

use test
go
if not object_id('T') is null
drop table T
Go
Create table T([Field1] nvarchar(3),[Field2] nvarchar(3))
Insert T
select N'aa1',N'bb1' union all
select N'aa2',N'bb2' union all
select N'aa3',N'bb3' union all
select N'aa4',N'bb4' union all
select N'aa5',N'bb5' union all
select N'aa6',N'bb6'
Go
declare @s varchar(8000)
select @s=isnull(@s+',','select ')+'[Field1]='+quotename([Field1],'''')+',[Field2]='+quotename([Field2],'''') from Texec (@s)
Field1 Field2 Field1 Field2 Field1 Field2 Field1 Field2 Field1 Field2 Field1 Field2
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
aa1    bb1    aa2    bb2    aa3    bb3    aa4    bb4    aa5    bb5    aa6    bb6(1 個資料列受到影響)

Answer 3

create table tb(Field1 varchar(10),Field2 varchar(10))
insert into tb values('aa1' ,'bb1') 
insert into tb values('aa2' ,'bb2') 
insert into tb values('aa3' ,'bb3') 
insert into tb values('aa4' ,'bb4') 
insert into tb values('aa5' ,'bb5') 
insert into tb values('aa6' ,'bb6')
go--静态SQL。
select 
  max(case px when 1 then field1 else '' end) f1_1,
  max(case px when 1 then field2 else '' end) f2_1,
  max(case px when 2 then field1 else '' end) f1_2,
  max(case px when 2 then field2 else '' end) f2_2,
  max(case px when 3 then field1 else '' end) f1_3,
  max(case px when 3 then field2 else '' end) f2_3,
  max(case px when 4 then field1 else '' end) f1_4,
  max(case px when 4 then field2 else '' end) f2_4,
  max(case px when 5 then field1 else '' end) f1_5,
  max(case px when 5 then field2 else '' end) f2_5,
  max(case px when 6 then field1 else '' end) f1_6,
  max(case px when 6 then field2 else '' end) f2_6
from
(
  select *,id=1, px = (select count(1) from tb where field1 < t.field1 or (field1 = t.field1 and field2 < t.field2)) + 1 from tb t
) m
group by id--动态SQL
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + ' max(case px when ''' + cast(px as varchar) + ''' then Field1 else '' '' end) [f1_' + cast(px as varchar) + '] ,'
                   + ' max(case px when ''' + cast(px as varchar) + ''' then Field2 else '' '' end) [f2_' + cast(px as varchar) + '] ,'
from (select distinct px from (select *,id=1, px = (select count(1) from tb where field1 < t.field1 or (field1 = t.field1 and field2 < t.field2)) + 1 from tb t) m) as a
set @sql = @sql + ' from (select *,id=1, px = (select count(1) from tb where field1 < t.field1 or (field1 = t.field1 and field2 < t.field2)) + 1 from tb t) m group by id'
set @sql = replace(@sql , ', from' , ' from')
exec(@sql) drop table tb/*
f1_1       f2_1       f1_2       f2_2       f1_3       f2_3       f1_4       f2_4       f1_5       f2_5       f1_6       f2_6       
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 
aa1        bb1        aa2        bb2        aa3        bb3        aa4        bb4        aa5        bb5        aa6        bb6（所影响的行数为 1 行）
*/

Answer 4

自定議列名顯示:
use test
go
if not object_id('T') is null
drop table T
Go
Create table T([Field1] nvarchar(3),[Field2] nvarchar(3))
Insert T
select N'aa1',N'bb1' union all
select N'aa2',N'bb2' union all
select N'aa3',N'bb3' union all
select N'aa4',N'bb4' union all
select N'aa5',N'bb5' union all
select N'aa6',N'bb6'
Go
declare @s varchar(8000),@i int
select @i=1
select @s=isnull(@s+',','select ')+'[Field'+rtrim(@i)+']='+quotename([Field1],'''')+',[Field'+rtrim(@i+1)+']='+quotename([Field2],''''),@i=@i+2 from T
exec (@s)Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9 Field10 Field11 Field12
------ ------ ------ ------ ------ ------ ------ ------ ------ ------- ------- -------
aa1    bb1    aa2    bb2    aa3    bb3    aa4    bb4    aa5    bb5     aa6     bb6(1 個資料列受到影響)

Answer 5

-->生成测试数据
 
drop table #
declare @tb table([Field1] nvarchar(3),[Field2] nvarchar(3))
Insert @tb
select N'aa1',N'bb1' union all
select N'aa2',N'bb2' union all
select N'aa3',N'bb3' union all
select N'aa4',N'bb4' union all
select N'aa5',N'bb5' union all
select N'aa6',N'bb6' union all
select N'aa7',N'bb7'
Select identity(int,1,1) id,* into # from @tbdeclare @sql nvarchar(4000)
declare @i int, @cnt int
select @i = 1,@sql = ' select ' ,@cnt = count(1) from #
while @i<=6
begin
    if @i = 1 set @sql =@sql +' max(case id%6 when '+ltrim(@i%6)+' then isnull([Field1],'''') end ) as col'+ltrim(@i)+' ,max(case id%6 when '+ltrim(@i%6)+' then isnull([Field2],'''') end ) as col'+ltrim(@i+1)+' ,'
    else set @sql =@sql +' max(case id%6 when '+ltrim(@i%6)+' then isnull([Field1],'''') end ) as col'+ltrim(2*@i-1)+' ,max(case id%6 when '+ltrim(@i%6)+' then isnull([Field2],'''') end ) as col'+ltrim(2*@i)+' ,'
    set @i = @i+1
end
set @sql = left(@sql,len(@sql)-1) + ' from # group by (id-1)/6 '
exec(@sql)
/*
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- -----
aa1  bb1  aa2  bb2  aa3  bb3  aa4  bb4  aa5  bb5   aa6   bb6
aa7  bb7  NULL NULL NULL NULL NULL NULL NULL NULL  NULL  NULL
*/