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 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management ...
其中,“行列转换”(即行转列或列转行)是一种常见的需求,尤其是在汇总报告、数据透视等场景下尤为重要。本文将通过一个具体的SQL Server示例来深入探讨如何实现行列转换。 #### 一、创建视图(Step 1) 首先,...
SQL 行列转换扩展 SQL 行列转换扩展是指将数据库表中的行数据转换为列数据,或者将列数据转换为行数据的操作。这种操作在数据分析和报表生成中非常常见。在本文中,我们将介绍两种类型的行列转换扩展方法:行转列...
### SQL语句实现表的行列转换,行转列,列转行 在处理数据库时,我们经常需要对数据进行各种变换以适应不同的分析需求。其中,“行列转换”就是一种非常实用的功能,它可以帮助我们将表中的行数据转换为列数据,...
通过学习这些SQL试题、行列转换技巧和存储过程实例,你可以全面提升SQL技能,无论是日常的数据查询还是复杂的业务逻辑处理,都能得心应手。在实际工作中,不断练习和探索,将理论知识转化为实践能力,是成为SQL高手...
### 通过SQL语句实现行列转换的几种方法 在日常工作中,我们经常需要处理的数据结构并不总是按照我们期望的方式组织的。特别是在制作自定义报表或进行产品开发时,经常会遇到需要将数据从一种布局转换到另一种布局...
### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...
### Oracle中SQL语句行列之间的相互转换 在Oracle数据库中,有时我们需要将表格中的行转换为列或将列转换为行,这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中...
### 行列转换SQL存储过程代码解析 #### 核心知识点概述 本篇文章将深入探讨一个SQL存储过程的实现方式,该存储过程主要用于完成“行转列”(即行列转换)的操作。通过这种方式,可以有效地将数据库表中的行数据...
在Oracle SQL中,行列转换是一种常见的数据操作需求,主要用于将数据表中的行转换为列,或将列转换为行,以此来满足不同的数据展示或分析需求。这种转换在处理多维度数据、汇总数据或是进行复杂查询时特别有用。下面...
### SQL查询中的行列转换 在数据库管理中,经常会遇到数据结构需要调整的情况,尤其是当原始数据的排列方式与实际需求不一致时。本篇文章将详细解释如何通过一条SQL查询语句实现行列转换,并且会针对两种不同的SQL ...
在SQL中,行列转换是一种常见的数据操作,尤其在数据分析和报表生成时尤为关键。动态添加列则是在查询过程中根据特定条件或数据集自动生成列,这在处理不断变化的数据结构时非常有用。以下是对这些主题的详细解释: ...
SQL 行列转换 Pivot UnPivot SQL 行列转换是指将表格中的行数据转换为列数据,或者将列数据转换为行数据。Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要...
### SQL 数据行列转换 #### 知识点概览 1. **SQL Server中的行列转换**:介绍如何在SQL Server环境中利用内置函数实现数据从行到列的转换。 2. **FOR XML PATH() 函数的应用**:详细解释FOR XML PATH()函数在行列...
### SQL语句普通行列转换详解 #### 一、背景与目的 本文档旨在介绍如何使用SQL Server中的行列转换功能来解决常见的数据展示需求。通过具体的示例,将原始的宽表(每一行代表一个学生的所有科目成绩)转换为长表...
1. **窗口函数**:SQL的窗口函数(如`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`等)可以与子查询结合使用,来实现行列转换。这种方法通常适用于复杂的场景,例如按时间序列进行数据转换。 四、数据仓库工具 1. **...
最后,使用`exec`语句执行动态构建的SQL语句,完成行列转换的数据处理。 ### 实际应用与注意事项 - **性能考虑**:动态构建SQL语句虽然灵活,但在涉及大量列转换时可能会影响性能。因此,在设计存储过程时,应考虑...
本文将深入解析一个Oracle行列转换的例子,通过详细解释SQL语句的构成及其背后的逻辑,帮助读者理解如何在Oracle中实现行列转换。 ### Oracle行列转换例子解析 #### SQL语句结构分析 给定的SQL语句主要分为几个...