行---列 转换 是牛人的就来挑战一下.

Question

转老乌龟的. 
SQL code*
普通行列转换(2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/create table tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when '语文' then result else 0 end) 语文,
  max(case subject when '数学' then result else 0 end) 数学,
  max(case subject when '物理' then result else 0 end) 物理,
  cast(avg(result*1.0) as decimal(18,2)) 平均分,
  sum(result) 总分
from tb
group by name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/drop table tb    ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94想变成 
Name       Subject Result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/create table tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name       Subject     Result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
  union all
  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
  union all
  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1

Answer 1

create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))insert tb select        '01151',               'A19'        ,         '01' 
insert tb select        '01151' ,              'A19'       ,          '02' 
insert tb select        '01151'  ,             'A19'      ,           '03' 
insert tb select        '01M75'   ,            'C07'     ,            '01' 
insert tb select        '01M75'    ,           'C07'    ,             '02' 
insert tb select        '01M75'     ,          'C07'   ,              '03' 
insert tb select        '01C14'      ,         'K93'  ,               '01' 
insert tb select        '01C14'       ,        'K93' ,                '02' 
insert tb select        '01C14'        ,       'K93',                 '03'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when code3='''+code3+''' then code3 end) ['+code3+']'
from (select distinct code3 from tb) aexec ('select code1,code2'+@sql+' from tb group by code1,code2')drop table tb/*
code1      code2      01         02         03         
---------- ---------- ---------- ---------- ---------- 
01151      A19        01         02         03
01M75      C07        01         02         03
01C14      K93        01         02         03
*/

Answer 2

create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))
insert tb select '01151' , 'A19' , '01' 
insert tb select '01151' , 'A19' , '02' 
insert tb select '01151' , 'A19' , '03' 
insert tb select '01M75' , 'C07' , '01' 
insert tb select '01M75' , 'C07' , '02' 
insert tb select '01M75' , 'C07' , '03' 
insert tb select '01C14' , 'K93' , '01' 
insert tb select '01C14' , 'K93' , '02' 
insert tb select '01C14' , 'K93' , '03'
goselect code1,code2,
  max(case code3 when '01' then code3 else '' end) [01],
  max(case code3 when '02' then code3 else '' end) [02],
  max(case code3 when '03' then code3 else '' end) [03]
from tb
group by code1,code2drop table tb/*
code1      code2      01         02         03
---------- ---------- ---------- ---------- ----------
01151      A19        01         02         03
01M75      C07        01         02         03
01C14      K93        01         02         03(3 行受影响)
*/

Answer 3

--静态和动态的意思上面转我的帖中已有说明.
create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))
insert tb select '01151' , 'A19' , '01' 
insert tb select '01151' , 'A19' , '02' 
insert tb select '01151' , 'A19' , '03' 
insert tb select '01M75' , 'C07' , '01' 
insert tb select '01M75' , 'C07' , '02' 
insert tb select '01M75' , 'C07' , '03' 
insert tb select '01C14' , 'K93' , '01' 
insert tb select '01C14' , 'K93' , '02' 
insert tb select '01C14' , 'K93' , '03'
go
--静态SQL
select code1,code2,
  max(case code3 when '01' then code3 else '' end) [01],
  max(case code3 when '02' then code3 else '' end) [02],
  max(case code3 when '03' then code3 else '' end) [03]
from tb
group by code1,code2
--动态SQL
declare @sql varchar(8000)
set @sql = 'select code1,code2'
select @sql = @sql + ' , max(case code3 when ''' + code3 + ''' then code3 else '' '' end) [' + code3 + ']'
from (select distinct code3 from tb) as a
set @sql = @sql + ' from tb group by code1,code2'
exec(@sql) drop table tb/*
code1      code2      01         02         03
---------- ---------- ---------- ---------- ----------
01151      A19        01         02         03
01M75      C07        01         02         03
01C14      K93        01         02         03(3 行受影响)
*/

Answer 4

呵,最近跟着高人们学了一手,小试牛刀.--借上面的例子
create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))insert tb select '01151',        'A19'        ,'01' 
insert tb select '01151' ,       'A19'       , '02' 
insert tb select '01151'  ,      'A19'      ,  '03' 
insert tb select '01M75'   ,     'C07'     ,   '01' 
insert tb select '01M75'    ,    'C07'    ,    '02' 
insert tb select '01M75'     ,   'C07'   ,     '03' 
insert tb select '01C14'      ,  'K93'  ,      '01' 
insert tb select '01C14'       , 'K93' ,       '02' 
insert tb select '01C14'        ,'K93',        '03'
declare @sql varchar(8000)
select @sql=isnull(@sql+',','select code1,code2,')
           +quotename(code3)
           +'=MAX(CASE WHEN CODE3='''+CODE3+''' THEN CODE3 ELSE '''' END)'
    from (select distinct code3 from tb ) aset @sql=@sql+' from tb group by code1,code2'exec(@sql)drop table tbcode1      code2      01         02         03
---------- ---------- ---------- ---------- ----------
01151      A19        01         02         03
01M75      C07        01         02         03
01C14      K93        01         02         03(3 行受影响)

Answer 5

呵呵！要是这样呢？
   已知一张表   Tab   (code1,code2,cod3,code4,code5)primary   key   3个字段为联合主键   现有以下数据记录: 
        code1               code2         code3      code4   code5
        01151               A19           01         02     03 
        01M75               C07           01         02     03 
        01C14               K93           01         02     03   
-----> 转换后查询结果为:
        code1             code2               code3 
        01151               A19                 01 
        01151               A19                 02 
        01151               A19                 03 
      
        01M75               C07                 01 
        01M75               C07                 02 
        01M75               C07                 03 
      
        01C14               K93                 01 
        01C14               K93                 02 
        01C14               K93                 03 
 
        能够转换吗？

Answer 6

用楼上的例子实践一下create table tab
(
 code1 char(5)
,code2 char(3)
,code3 char(2)
,code4 char(2)
,code5 char(2)
)insert tab values('01151','A19','01','02','03')
insert tab values('01M75','C07','01','02','03')  
insert tab values('01C14','K93','01','02','03')
--静态
select * from
(
select code1,code2,[code3]=code3 from tab
union all
select code1,code2,[code3]=code4 from tab
union all
select code1,code2,[code3]=code5 from tab
) a 
order by code1,code2,code3
--动态
DECLARE @sql VARCHAR(8000)SELECT @sql=ISNULL(@sql+' UNION ALL ','')
            +'SELECT code1,code2,code3='
            +QUOTENAME(name) 
            +'FROM tab'
  FROM (SELECT name FROM syscolumns WHERE syscolumns.[id]=object_id('tab') AND                                        syscolumns.[name] not in('code1','code2')) adrop table tab/*
--结果
code1 code2 code3
----- ----- -----
01151 A19   01
01M75 C07   01
01C14 K93   01
01151 A19   02
01M75 C07   02
01C14 K93   02
01151 A19   03
01M75 C07   03
01C14 K93   03(9 行受影响)*/

Answer 7

--动态
DECLARE @sql VARCHAR(1000)SELECT @sql=ISNULL(@sql+' UNION ALL ','')
            +'SELECT code1,code2,code3='
            +QUOTENAME(name) 
            +'FROM tab'
  FROM (SELECT [name] FROM syscolumns WHERE syscolumns.[id]=object_id('tab') 
                                       and syscolumns.[name] not in('code1','code2')) a
exec(@sql)

Answer 8

呵呵！要是这样呢？ 
      已知一张表       Tab       (code1,code2,cod3,code4,code5)primary       key       3个字段为联合主键       现有以下数据记录:   
                code1                               code2                   code3             code4       code5 
                01151                               A19                       01                   02           03   
                01M75                               C07                       01                   02           03   
                01C14                               K93                       01                   02           03       
----->   转换后查询结果为: 
                code1                           code2                               code3   
                01151                               A19                                   01   
                01151                               A19                                   02   
                01151                               A19                                   03   
            
                01M75                               C07                                   01   
                01M75                               C07                                   02   
                01M75                               C07                                   03   
            
                01C14                               K93                                   01   
                01C14                               K93                                   02   
                01C14                               K93                                   03   
  
                能够转换吗？create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10),code4 varchar(10),code5 varchar(10))
insert into tb 
select '01151','A19','01','02','03'
union all
select '01M75','C07','01','02','03'
union all
select '01C14','K93','01','02','03'
goSELECT  code1,code2,code_3
FROM 
   (SELECT code1,code2, code3, code4, code5
   FROM tb) p
UNPIVOT
   (code_3 FOR Employee IN 
      (code3, code4, code5)
)AS unpvt
go
drop table tb
go
/*
(3 行受影响)
code1      code2      code_3
---------- ---------- ----------
01151      A19        01
01151      A19        02
01151      A19        03
01M75      C07        01
01M75      C07        02
01M75      C07        03
01C14      K93        01
01C14      K93        02
01C14      K93        03(9 行受影响)*/

Answer 9

[code=create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10),code4 varchar(10),code5 varchar(10))
insert into tb 
select '01151','A19','01','02','03'
union all
select '01M75','C07','01','02','03'
union all
select '01C14','K93','01','02','03'
goSELECT  code1,code2,code_3
FROM 
   (SELECT code1,code2, code3, code4, code5
   FROM tb) p
UNPIVOT
   (code_3 FOR Employee IN 
      (code3, code4, code5)
)AS unpvt
go
drop table tb
go
][/code]