SQL 行列转换

行列转换等经典SQL语句
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 cj --创建表cj
(
ID Int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
Name Varchar(50),
Subject Varchar(50),
Result Int,
primary key (ID) --定义ID为表cj的主键
);
--Truncate table cj
--Select from cj
Insert into cj
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 = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj --把所有唯一的科目的名称都列举出来
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,dboKFReturn(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')

--mssql:
with tmp(col) as
 (select '1111' union all 
  select '2222' union all 
  select '3333' union all
  select '4444'),
tmp1(col,col_new,level) as
 (select col, cast(SUBSTRING(col, 1, 1) as nvarchar(10)), 1
    from tmp
  union all
  select col, cast(SUBSTRING(col, level + 1, 1) as nvarchar(10)), level + 1
    from tmp1
   where level < LEN(col))
select (select col_new + ''
          from tmp1
         where level = tlevel
         order by col_new
           for xml path('')) as col
  from tmp1 t
 group by level;
 --oracle:
 with tmp(col) as
 (select 1111  level from dual connect by level <= 4)
select listagg(a) within group(order by a) as col
  from (select col, substr(col, column_value, 1) a, column_value b
          from tmp,
               table(cast(multiset (select level
                             from dual
                           connect by level <= length(col)) as
                          sysodcinumberlist)))
 group by b

Create Table T
(
ID int,
F varchar(10)
) 
Insert Into T Values(1,'A')
Insert Into T Values(2,'B')
Insert Into T Values(1,'C')
Insert Into T Values(2,'D')
--单独行列转换
Select  From T 
Pivot
(
Sum(id)
For F in ([A],[B],[C],[D])
) As pvt
--按F分组,再行列转换
Select  From 
(
Select F,SUM(id) AS id From T  Group By F
) b
Pivot
(
Sum(id)
For F in ([A],[B],[C],[D])
) As pvt

drop table t;
create table t
(t1 int,
t2 int,
t3 int,
t4 int,
t5 int
)
/
insert into t values(1,2,3,4,5);
select from t;
select decode(rn,1,t1,2,t2,3,t3,4,t4,5,t5) val
from t,(select rownum rn from dual connect by rownum<=5);

现将表中要查询的车辆转换为列头创建表,然后通过列头将数据查出,插入到相应的列中,所有上车了数据插入完成后进行合计项插入基本就这些,排序就先知道需要的顺序,然后插入的时候加入编号,在用编号排序

---