sql行列转换方法整理
- 格式：pdf
- 大小：324.49 KB
- 文档页数：10
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,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')
4. 如何取得一个数据表的所有列名
方法如下：先从sysobjects系统表中取得数据表的systemid,然后再syscolumns表中
取得该数据表的所有列名。
SQL语句如下： Declare @objid int,@objname char(40) set @objname = 'ColumnToRow'
--第1种方法
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
--或也可以写成
select name as 'Column_name' from syscolumns where id = @objid order by colid
--第2种方法:
Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid
5. 通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
Exec Sp_password '原始密码','更改后密码','账号' Exec sp_password null,ok,sa
6. 怎么判断出一个表的哪些字段不允许为空？
Declare @objname Varchar(50) set @objname = 'ColumnToRow'
Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname
7. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况 Select a.name as Columnname,b.name as tablename from SysColumns a
inner join sysobjects b on a.id = b.id
and b.type = 'U' and a.name = '您要查找的字段名'
b. 未知列名查所有在不同表出现过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)
