SQL Server使用PIVOT与unPIVOT实现行列转换

一、sql行转列：PIVOT

1、基本语法：

create table #table1
    (    id int ,code varchar(10) , name varchar(20) );
go

insert into #table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2,  'm2',null ), ( 3, 'm3', 'c' ), ( 4,  'm2','d' ), ( 5,  'm1','c' );
go

select * from #table1;

--方法一（推荐）
select PVT.code, PVT.a, PVT.b, PVT.c
      from #table1 pivot(count(id) for name in(a, b, c)) as PVT;

--方法二
with P as (select * from #table1)
select PVT.code, PVT.a, PVT.b, PVT.c
     from P        pivot(count(id) for name in(a, b, c)) as PVT;
drop table #table1;

结果：

2、实例：

3、传统方式：（先汇总拼接出所需列的字符串，再动态执行转列）

先查询出要转为列的行数据，再拼接字符串。

create table #table1
    (    id int ,code varchar(10) , name varchar(20) );
go

insert into #table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2,  'm2',null ), ( 3, 'm3', 'c' ), ( 4,  'm2','d' ), ( 5,  'm1','c' );
go

select * from #table1;

declare @strCN nvarchar(100);
select @strCN = isnull(@strCN + ',', '') + quotename(name) from #table1 group by name ;
print  @strCN  --‘[a],[c],[d]'
declare @SqlStr nvarchar(1000);

set @SqlStr = N'
select * from #table1 pivot ( count(ID) for name in (' + @strCN + N') ) as PVT';
exec ( @SqlStr );

drop table #table1;

结果：

二、sql列转行：unPIVOT：

基本语法：

create table #table1 (id int,
code varchar(10),
name1 varchar(20),
name2 varchar(20),
name3 varchar(20));
go
insert into #table1(id, name1, name2, code, name3)
values(1, 'm1', 'a1', 'a2', 'a3'),
    (2, 'm2', 'b1', 'b2', 'b3'),
    (4, 'm1', 'c1', 'c2', 'c3');
go
select * from #table1;

--方法一
select PVT.id, PVT.code, PVT.name, PVT.val
            from #table1 unpivot(val for name in(name1, name2, name3)) as PVT;
--方法二
with P as (select * from #table1)
select PVT.id, PVT.code, PVT.name, PVT.val
            from P       unpivot(val for name in(name1, name2, name3)) as PVT;
drop table #table1;

结果：

实例：

到此这篇关于SQL Server使用PIVOT与unPIVOT实现行列转换的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • sqlserver2005 行列转换实现方法

    复制代码 代码如下: --Create Company Table Create Table Company ( ComID varchar(50) primary key, ComName nvarchar(50) not null, ComNumber varchar(50) not null, ComAddress nvarchar(200), ComTele varchar(50) ) --Create Product Table Create Table Product ( Produ

  • SQL Server 使用 Pivot 和 UnPivot 实现行列转换的问题小结

    SQL Server 使用 Pivot 和 UnPivot 实现行列转换的问题小结

    对于行列转换的数据,通常也就是在做报表的时候用的比较多,之前也零零散散的看了一些,今天就来总结一下. 先创建一个用于演示的临时表: create table #temp ( 年份 nvarchar(10) null, 月份 nvarchar(10) null, 数量 int null ) insert into #temp(年份,月份,数量) select '2015','1','5645' union select '2015','2','1234' union select '2015','

  • SQL Server基础之行数据转换为列数据

    SQL Server基础之行数据转换为列数据

    准备工作 创建表 use [test1] go create table [dbo].[student]( [id] [int] identity(1,1) not null, [name] [nvarchar](50) null, [project] [nvarchar](50) null, [score] [int] null, constraint [pk_student] primary key clustered ( [id] asc )with (pad_index = off, s

  • sql server通过pivot对数据进行行列转换的方法

    脚本: /* 说明:sql server如何通过pivot对数据进行行列转换 */ create table #t ( [员工工号] nvarchar(50), [员工姓名] nvarchar(50), [月份] nvarchar(50), [工资] int ) insert into #t values(N'01', N'张三', N'2020年01月', 2400) ,(N'02', N'李四', N'2020年01月', 3000) ,(N'01', N'张三', N'2020年02月',

  • sqlserver下将数据库记录的列记录转换成行记录的方法

    假设有张学生成绩表(tb)如下: Name Subject Result 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 想变成 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94 张三 74 83 93 SQL 语句如下: 复制代码 代码如下: create table tb ( Name varchar(10) , Subject varcha

  • SQL Server使用PIVOT与unPIVOT实现行列转换

    SQL Server使用PIVOT与unPIVOT实现行列转换

    一.sql行转列:PIVOT 1.基本语法: create table #table1 ( id int ,code varchar(10) , name varchar(20) ); go insert into #table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2, 'm2',null ), ( 3, 'm3', 'c' ), ( 4, 'm2','d' ), ( 5, 'm1','c' ); go select * from #tabl

  • Sql实现行列转换方便了我们存储数据和呈现数据

    Sql实现行列转换方便了我们存储数据和呈现数据

    从MS Sql Server 2005微软就推出了pivot和unpivot实现行列转换,这极大的方便了我们存储数据和呈现数据.今天就对这两个关键字进行分析,结合实例讲解如何存储数据,如何呈现数据. 例如学生选课和成绩系统中就有一张表,该表存储了学生的课程成绩,我们无法去预料课程的多少,因此一般表会设计为下面这样: 图1  最后一列是课程编号,这样无论开学之后还会不会增加课程供学生选择,都没有关系.那么我们要呈现给用户看的报表一般是这样的: 图2  可以看到存储数据的时候采用的是列式存储,最终呈

  • asp.net开发sql server转换成oracle的方法详解

    asp.net开发sql server转换成oracle的方法详解

    前言 因为前段时间我们公司项目 要把sql server 转oracle,发现网上这方面的资料较少,所以在这里分享一下心得,也记录一下问题,下面话不多说了,来一起看看详细的介绍: 开始我研究了一段时间 然后下载了 oracle 11g 版本 和 PL/SQL(客户端) 和sql server 不同的是 oracle 没有自己的客户端 需要用第三方的软件运行 PL/SQL 就是一个 sqldeveloper 也是一个,PL/SQL 我觉得比较稳定一点.但是2个都安装的话 刚好互补了 oracle

  • SQL Server数据类型转换方法

    SQL Server数据类型转换方法

    在SQL Server日常的函数.存储过程和SQL语句中,经常会用到不同数据类型的转换.在SQL Server有两种数据转换类型:一种是显性数据转换:另一种是隐性数据转换.下面分别对这两种数据类型转换进行简要的说明: 1 显式转换 显示转换是将某种数据类型的表达式显式转换为另一种数据类型.常用的是CAST 和 CONVERT 函数. CAST: CAST ( expression AS data_type )  CONVERT: CONVERT (data_type[(length)], exp

  • 深入SQL中PIVOT 行列转换详解

    深入SQL中PIVOT 行列转换详解

    PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合.UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值. 通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行 一.PIVOT实例 1. 建表 建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表示销售额.quarter字段分别用Q1, Q2, Q3, Q4表示一.二.三.四季度. CREATE TABLE

  • 行转列之SQL SERVER PIVOT与用法详解

    在数据库操作中,有些时候我们遇到需要实现"行转列"的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME SELECT '星期一',1000 UNION ALL SELECT '星期二',2000 UNION ALL SELECT '星期三',3000 UNION ALL SELECT '星期四',4000 UNION ALL SE

  • 浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

    /*创建数据库*/ CREATE DATABASE tmp go USE tmp go /*创建数据库测试表*/ CREATE TABLE [Scores]     (       [ID] INT IDENTITY(1, 1)                PRIMARY KEY ,       [Student] VARCHAR(20) ,       [Subject] VARCHAR(30) ,       [Score] FLOAT     ) go TRUNCATE TABLE Sc

  • SQL Server 2008的逻辑查询处理步骤

    SQL Server 2008的逻辑查询处理步骤

    最近我则刚刚收到了微软最有价值专家项目组寄来的该书的SQL Server 2008英文版本-<Inside Microsoft SQL Server 2008:T-SQL Querying>(http://www.microsoft.com/learning/en/us/book.aspx?ID=12804&locale=en-us),大致翻了一下,发现内容变动不少,既有新增的章节,也有基于SQL Server 2005或2008新技术重新书写的SQL查询示例. 我先就逻辑查询处理步骤

随机推荐