SQL Server 行转列和列转行实例讲解

1、列转行

数据经过计算加工后会直接生成前端图表需要的数据源,但是程序里又需要把该数据经过列转行写入中间表中,下次再查询该数据时直接从中间表查询数据。

1.1 列换行语法

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

1.2  行转列案例

WITH T
AS
(
    SELECT 1 as TeamId,'测试团队1' as Team,80 'MEN',20 'WOMEN'
    UNION
    SELECT 2 as TeamId,'测试团队2' as Team,30 'MEN',70 'WOMEN'
)

---列转行------------------------------------

SELECT TeamId,Team ,TYPE=ATTRIBUTE,CNT=VALUE
FROM T
UNPIVOT (
  VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN])
) AS UPV

2、 行转列

行转列主要是从中间表里查询数据,SQL SERVER2005以下的版本则可以使用聚合函数来完成。

2.1 行转列语法

table_source

PIVOT(

聚合函数(value_column)

FOR pivot_column

IN(<column_list>)

)

2.2、使用PIVOT实现

WITH T
AS
(
  SELECT  1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT
  UNION
  SELECT  1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT
  UNION
  SELECT  2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT
  UNION
  SELECT  2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT
)

SELECT * FROM T PIVOT (SUM(CENT) FOR ITEM IN ([MEN],[WOMEN])) A

2.3、使用聚合函数实现

WITH T
AS
(
  SELECT  1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT
  UNION
  SELECT  1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT
  UNION
  SELECT  2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT
  UNION
  SELECT  2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT
)

SELECT ID,TEAM,
SUM(CASE WHEN ITEM='MEN' THEN CENT ELSE 0 END) 'MEN',
SUM(CASE WHEN ITEM='WOMEN' THEN CENT ELSE 0 END) 'WOMEN'
FROM T
GROUP BY ID,TEAM

sql server 行转列,列转行函数

-- 注:支持sqlserver2005 及以上版本
--drop table tb
--create table tb(name varchar(10),course varchar(10),score int,sex char(4))
      
insert into tb values('张三','语文',74,'男')
 
insert into tb values('张三','数学',83,'男')
 
insert into tb values('张三','物理',93,'男')
insert into tb values('张三','english',60,'女')
   
insert into tb values('李四','语文',74,'女')
 
insert into tb values('李四','数学',84,'女')
 
insert into tb values('李四','物理',94,'女')
 
SELECT * FROM tb
    
    
--行转列
SELECT * FROM (
    SELECT * FROM TB PIVOT(sum(score) FOR course IN (语文,数学,物理,english)) a
) B
order by name desc
 

--列转行
SELECT * FROM
(
SELECT * FROM TB
PIVOT(MAX(score) FOR course IN (语文,数学,物理)) a
)
B UNPIVOT( score for course in([语文],[数学],[物理]) ) c

SQLSERVER行转列和列转行

1: 行转列

子查询,获取一定数据集结果

SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN
(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BY count(1) DESC)
GROUP BY objid,action

下面用 行转列语法获取 最终结果

 select *
from  
(
SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN
(SELECT TOP 10 objid FROM T_MyAttention tma GROUP BY objid ORDER BY count(1) DESC)
GROUP BY objid,action
) t
pivot ( sum(count) for t.action in ([1],[2],[3],[4])) as ourpivot

微软官方的图:

2: 列转行

怎么把一条记录拆分成几条记录?

User     No.         A           B            C
1        1           21          34           24
1        2           42          25           16

RESULT:

User     No.        Type       Num
1        1          A          21
1        1          B          34
1        1          C          24
1        2          A          42
1        2          B          25
1        2          C          16

declare @t table(usser int ,no int ,a int,b int, c int)
insert into @t select 1,1,21,34,24
union all select 1,2,42,25,16

SELECT usser,no,Type=attribute, Num=value
FROM @t
  UNPIVOT
  (
    value FOR attribute IN([a], [b], [c])
  ) AS UPV
       
--结果

/*

usser   no       Type      num
----         ---      --------  --------
1           1         a          21
1           1         b          34
1           1         c          24
1           2         a          42
1           2         b          25
1           2         c           16
*/

时间: 2024-09-23 18:24:34

SQL Server 行转列和列转行实例讲解的相关文章

SQL Server数据库删除数据集中重复数据实例讲解_MsSql

SQL Server数据库操作中,有时对于表中的结果集,满足一定规则我们则认为是重复数据,而这些重复数据需要删除.如何删除呢?本文我们通过一个例子来加以说明. 例子如下: 如下只要companyName,invoiceNumber,customerNumber三者都相同,我们则认为是重复数据,下面的例子演示了如何删除. declare @InvoiceListMaster table ( ID int identity primary key , companyName Nchar(20), i

SQL Server数据库删除数据集中重复数据实例讲解

SQL Server数据库操作中,有时对于表中的结果集,满足一定规则我们则认为是重复数据,而这些重复数据需要删除.如何删除呢?本文我们通过一个例子来加以说明. 例子如下: 如下只要companyName,invoiceNumber,customerNumber三者都相同,我们则认为是重复数据,下面的例子演示了如何删除. declare @InvoiceListMaster table ( ID int identity primary key , companyName Nchar(20), i

浅析SQL Server数据库中的伪列以及伪列的含义

原文:浅析SQL Server数据库中的伪列以及伪列的含义   本文出处:http://www.cnblogs.com/wy123/p/6833116.html      SQL Server中的伪列 下午看QQ群有人在讨论(非聚集)索引的存储,说,对于聚集索引表,非聚集索引存储的是索引键值+聚集索引键值:对于非聚集索引表,索引存储的是索引键值+RowId,这应该是一个常识,对此不作具体详细阐述.这里主要是提到的RowId引起了一点思考.那么,这个RowId是个什么玩意?能不能更加直观一点来看看

Sql Server 性能优化之包含列

原文:Sql Server 性能优化之包含列 Sql Server 性能优化之包含列        导读:数据数优化查询一直是个比较热门的话题,小生在这方面也只能算是个入门生.今 天我们就讲下数据库包含列这个一项的作用及带来的优化效果           引用下MSDN里面的一段解释:        当查询中的所有列都作为键列或非键列包含在索引中时,带有包含性非键列的索引可以显 著提高查询性能. 这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值:不 访问表或聚集索引数据,从而减少磁盘

SQL Server如何在变长列上存储索引

原文:SQL Server如何在变长列上存储索引 这篇文章我想谈下SQL Server如何在变长列上存储索引.首先我们创建一个包含变长列的表,在上面定义主键,即在上面定义了聚集索引,然后往里面插入80000条记录: 1 -- Create a new table 2 CREATE TABLE Customers 3 ( 4 CustomerName VARCHAR(255) NOT NULL PRIMARY KEY, 5 Filler CHAR(138) NOT NULL 6 ) 7 GO 8

SQL Server表中添加新列并添加描述_MsSql

注: sql server 2005 及以上支持. 版本估计是不支持(工作环境2005,2008). 工作需要, 需要向SQL Server 现有表中添加新列并添加描述. 从而有个如下存储过程. (先附上存储过程然后解释) /********调用方法********** 作用: 添加列并添加列描述信息 调用: exec [SetColumnInfo] '表名', '列名', N'列说明,描述','列类型{默认:NVARCHAR(50)}','列默认值{默认:NULL}' ************

SQL Server表中添加新列并添加描述

注: sql server 2005 及以上支持. 版本估计是不支持(工作环境2005,2008). 工作需要, 需要向SQL Server 现有表中添加新列并添加描述. 从而有个如下存储过程. (先附上存储过程然后解释) /********调用方法********** 作用: 添加列并添加列描述信息 调用: exec [SetColumnInfo] '表名', '列名', N'列说明,描述','列类型{默认:NVARCHAR(50)}','列默认值{默认:NULL}' ************

Sql Server中判断表、列不存在则创建的方法

一.Sql Server中如何判断表中某列是否存在 首先跟大家分享Sql Server中判断表中某列是否存在的两个方法,方法示例如下: 比如说要判断表A中的字段C是否存在两个方法: 第一种方法 IF EXISTS ( SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID WHERE T1.NAME='A' AND T2.NAME='C' ) PRINT '存在' ELSE PRINT '不存在' 第二种方法,短小精

SQL Server 2000 汉字数据简繁转换实例 (COM,.Net)

server|汉字|简繁转换|数据 SQL Server 2000 汉字数据简繁转换实例 (COM,.Net) 首先确认在 Windows (当然只是 SQL Server 的服务器端) 上安装了 .Net Framework 1.1 ! 确认成功安装后:1.用 sn -k 为该类库创建一个强名密钥文件: 在安装 Microsoft .Net Framework SDK 的所在目录下的 v1.1\Bin\ 子目录下执行如下命令行: sn.exe -k c:\snkey.snk 2.打开记事本(N