行列转换

1.--行列转换 另例
原表:   姓名     科目   成绩
           张三     语文    80
           张三     数学    90
           张三     物理    85
           李四     语文    85
           李四     物理    82
           李四     英语    90
           李四     政治    70
           王五     英语    90

转换后的表：  姓名       数学    物理     英语    语文    政治
                       李四         0         82        90      85       70
                       王五         0          0         90       0         0
                       张三        90        85         0       80        0

实例:

create table score  --创建成绩表score
(
    ID       Int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    Name     Varchar(50),   
    Subject  Varchar(50),
    Result   Int,  
    primary key (ID)      --定义ID为表cj的主键      
);
Insert into score
Select '张三','语文',80 union all 
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语文',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
--行列转换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
--select @sql=''  from tb的原型
--注意：select @sql=',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']' 
--      from (select distinct Subject from score)score 返回的是最后一个科目
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from score)score  --把所有唯一的科目的名称都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)

2. 行列转换--合并
原表:   班级    学号    
            1          1 
            1          2
            1          3
            2          1
            2          2
            3          1
转换后的表:  班级  学号           
                       1   1,2,3
                       2   1,2
                       3   1

Create table ClassNo  --创建表ClassNo
 (
     ID Int IDENTITY(1,1)  not null,  --创建列ID,并且每次新增一条记录就会加1
     Class  Varchar(50),    --班级列
     Number Varchar(50),    --学号列
     Primary Key(ID)        --定义ID为表ClassNo的主键
 );
 --Truncate Table ClassNo
 --Select * from ClassNo
 Insert Into ClassNo
 Select 1,1 Union all
 Select 1,2 Union all
 Select 1,3 Union all
 Select 2,1 Union all
 Select 2,2 Union all
 Select 3,1
 
创建一个合并的函数
 --Drop Function KFReturn
 Create Function KFReturn(@Class Varchar(50))
 Returns Varchar(8000)
 as 
Begin
 Declare @str Varchar(8000)
 Set @str = ''
 Select @str = @str + cast(Number as Varchar(50))  + ',' from ClassNo Where Class = @Class 
Set @str = SubString(@str,1,len(@str)-1)
 Return(@str)
 End
 
--调用自定义函数得到结果
 Select Distinct Class,dbo.KFReturn(Class) From ClassNo

 

3:列转行
 --Drop Table ColumnToRow
 Create table ColumnToRow
 (
    ID Int IDENTITY(1,1)  not null,  --创建列ID,并且每次新增一条记录就会加1
    a  int,
    b  int,
    c  int,
    d  int,
    e  int,
    f  int,
    g  int,
    h  int,
    Primary Key(ID)        --定义ID为表ColumnToRow的主键      
);
 --Truncate Table ColumnToRow 
--Select * from ColumnToRow
 Insert Into ColumnToRow 
Select 15,9,1,0,1,2,4,2 Union all
 Select 22,34,44,5,6,7,8,7 Union all
 Select 33,44,55,66,77,88,99,12
 
Declare @sql Varchar(8000)
 Set @sql = ''
 Select @sql = @sql + rtrim(name) + ' from ColumnToRow union all Select ' from SysColumns Where id = object_id('ColumnToRow')
 Set @sql = SubString(@sql,1,len(@sql)-70)
 --70的长度就是这个字符串'from ColumnToRow union all Select ID from ColumnToRow union all Select ',因为它会把ID这一列的值也算进去，所以要把它截掉
 Exec ('Select ' + @sql + ' from ColumnToRow')