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)
8.查询第N行数据
假设id是主键:
select *
from (select top N * from 表) aa
where not exists(select 1 from (select top N-1 * from 表) bb where
aa.id=bb.id)
9. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm,
DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk,
DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq,
DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h.
本月的第一个星期一
select DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
i.
本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
相关推荐
SQL语句行列转换(附带数据库、表、视图操作) ,不错的文档。
Sql语句实现表的行列转换,行转列,列转行
本人在工作中遇到了涉及到数据库行列之间相互转换的问题,在网上搜索了很久也没有一个比较完整的解释,通过本人自己的摸索测试,整理出来了Oracle中SQL语句行列之间相互转换的资料,大家可以下载学习。
1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
SQL 行列转换 Pivot UnPivot SQL 行列转换是指将表格中的行数据转换为列数据,或者将列数据转换为行数据。Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要...
收集了一些,比较实用的sql语句和教程,SQL参考手册;行列转换等
通过一条查询语句,进行行列转换。例如 姓名 课程 分数 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 想变成(得到如下结果): 姓名 语文 数学 物理 ---- ---- ---- ---- 李四 ...
java面试题之行列转换 Oracle 和SQLServer两种方式 详细例子以及说明 一条语句实现,绝对看得懂! 方便理解!
在SQLserver中 直接用sql 语句将数据的行转换为列
一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A...
使用SQL语句,实现数据库查询结果的行列互换。
SQL 实现行列互换。例题,可以仿照写自己的SQL语句!注意语法
SQL语句实现行转列的几种方法示例,在SQL查询中经常需要行转列,文档中详细介绍了几种方法并对比
table动态行列数据转换,希望能帮到大家,是C#代码行列转换,告别传统的sql语句行列转换!
Oracle 行列转换技巧详解 Oracle 行列转换是指将数据库表中的行转换为列或将列转换为行的一种操作。这种操作在实际应用中非常有用,例如在数据报表生成、数据分析和数据挖掘等领域。下面我们将详细介绍 Oracle 行列...
SQL语句行列转换的两种方法 case...when和pivot函数应用,运用pivot 函数只支持数据库版本2005以上的。一般运用case when else end 的方法比较多,比较普遍
首先说明这个是收集与csdn 多年来的精华帖 ...包括备份还原,导入导出、行列转换、动态sql基本语法、游标、复制 等等,可以说是丰富多彩、包罗万象。 这里不多在阐述,绝对的物超所值,谁下谁知道。
1.创建数据库表及插入数据 2.子查询统计不同性质的学生总数 3.一行数据转换成两列数据 union all 4.表行列数据转换(表转置) 1.创建数据库表及插
│ │ 3.6.4 动态他Transact-SQL语句处理中的常见问题演示.sql │ │ 3.7.3 text与ntext字段的复制和合并处理示例.sql │ │ 3.7.4 text与image字段转换处理示例.sql │ │ 3.7.5 ntext字段的REPLACE处理示例.sql │...
-列车车次查询.sql │ │ 3.6.2 字符串在编号查询中的应用示例及常见问题.sql │ │ 3.6.3 动态参数的存储过程示例.sql │ │ 3.6.4 动态他Transact-SQL语句处理中的常见问题演示.sql │ │ 3.7.3 ...