【转】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,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))




本文转自齐师傅博客园博客,原文链接:http://www.cnblogs.com/youring2/archive/2012/05/25/2517956.html,如需转载请自行联系原作者

相关文章
|
2月前
|
SQL HIVE 索引
【Hive SQL 每日一题】行列转换
该文介绍了如何使用SQL进行数据的行列转换。首先展示了行转列的例子,通过创建一个学生成绩表,利用`IF`和`SUM`函数按学生ID分组,将每个学生的各科成绩转换为独立列。然后,文章讲述了列转行的需求,利用`LATERAL VIEW`和`POSEXPLODE`将已转换的表格恢复为原始行格式,通过索引匹配过滤笛卡尔积避免错误结果。此外,还提到了使用`UNION ALL`的另一种列转行方法。
|
11月前
|
SQL Oracle 关系型数据库
【SQL应知应会】行列转换(三)• Oracle版
【SQL应知应会】行列转换(三)• Oracle版
125 0
|
11月前
|
存储 SQL Oracle
【SQL应知应会】行列转换(二)• MySQL版
【SQL应知应会】行列转换(二)• MySQL版
245 0
|
SQL 存储 Oracle
通过sql做数据透视表,数据库表行列转换(pivot和Unpivot用法)(一)
在mssql中大家都知道可以使用pivot来统计数据,实现像excel的透视表功能 一、MSsqlserver中我们通常的用法
349 0
|
SQL HIVE
SQL之行列转换
Hive 查询语句之行列转换
137 0
|
存储 SQL 分布式计算
MaxCompute SQL使用小技巧之行列转换
行列转换在业务需求分析经常使用,方法很多,这里介绍下使用Maxcomputer内置函数进行转换
1309 0
|
SQL 索引 存储
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
原文:SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句 原文出处:http://www.cnblogs.com/wy123/p/5933734.html     先看常用的一种表结构设计方式:   那么可能会遇到一种典型的查询方式,主子表关联,查询子表中的某些(或者全部)Key点对应的Value,横向显示(也即以行的方式显示)   这种查询方式很明显的一个却显示多次对字表查询(暂时抛开索引)   相比这种查询方式很多人都遇到过,如果子表是配置信息之类的小表的话,问题不大,如果字表数据量较大,可能就会有影响了。
1200 0
|
SQL 数据安全/隐私保护