SQL Server 查询语句Pivot详解

sql server unpivot用法

sql server unpivot用法
SQLServer中的Unpivot用法是什么？Unpivot是一种数据转换操作，它可以将表格的列转换为行，使数据更加易于分析和比较。

当我们需要对数据进行不同类型的聚合和分析时，Unpivot是非常有用的。

在SQL Server中，Unpivot操作可以使用UNPIVOT运算符来实现。

该操作可以将列名转换为行中的值，并将所有列转换为两个列：一个包含原始列的列名，另一个包含原始列中的值。

下面是一个示例： SELECT CustomerID, Product, SalesDate, SalesAmount
FROM
(SELECT CustomerID, Product1, Product2, Product3, SalesDate,
Sales1, Sales2, Sales3
FROM SalesTable) p
UNPIVOT
(SalesAmount FOR Product IN
(Product1, Product2, Product3)
)AS unpvt;
在上面的示例中，UNPIVOT操作将原始表中的三列Product1、Product2和Product3转换为一列Product，并将每个值从原始列中转换为一个值。

这使得我们可以更轻松地分析和比较数据。

总之，SQL Server中的Unpivot用法可以帮助我们轻松地转换
表格数据，使其更加易于分析和比较。

它在数据聚合和分析方面非常有用。

oracle的pivot函数

oracle的pivot函数Oracle的PIVOT函数是一种非常强大的数据转换工具，它可以将行数据转换为列数据，使得数据分析和报表生成更加方便和灵活。

本文将详细介绍Oracle的PIVOT函数的使用方法和实际应用场景。

一、什么是PIVOT函数PIVOT函数是Oracle数据库中的一个聚合函数，它可以将行数据转换为列数据。

它基于一个或多个列的值动态生成列，并以这些列作为新的列名，然后将行数据填充到对应的列中。

这样就可以将原本以行形式存储的数据转换为以列形式存储的数据。

二、PIVOT函数的语法和用法PIVOT函数的语法如下：```SELECT *FROM (SELECT 列1, 列2, 列3 FROM 表名)PIVOT (聚合函数(列n)FOR 列n IN (列值1, 列值2, 列值3, ... ))```其中，聚合函数可以是SUM、COUNT、AVG等常见的聚合函数，列n 是需要转换为列的列名，列值1、列值2、列值3等是列n中可能出现的不同取值。

可以根据实际需求自行修改。

三、实际应用场景PIVOT函数在实际应用中非常有用，可以解决很多数据分析和报表生成的问题。

下面我们通过实际案例来说明其用法和应用场景。

假设我们有一个订单表，其中包含了订单的编号、日期和金额等信息。

现在我们需要将订单按日期分组，并统计每天的订单金额总额。

使用PIVOT函数可以很方便地实现这个需求。

我们创建一个名为orders的表，包含了订单的编号、日期和金额等字段。

然后，我们可以使用下面的SQL语句来实现需求：```SELECT *FROM (SELECT TO_CHAR(订单日期, 'YYYY-MM-DD') AS 日期, 订单金额FROM orders)PIVOT (SUM(订单金额)FOR 日期IN ('2021-01-01', '2021-01-02', '2021-01-03', ... ))```以上SQL语句中，我们先将日期字段转换为指定的格式，然后使用PIVOT函数对订单金额进行求和，并以日期作为列名。

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*FROM[TestRows2Columns]（图2：样本数据）(二) 先以静态的方式实现行转列，效果如图3所示：--1：静态拼接行转列SELECT[UserName],SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END) AS'[数学]',SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END) AS'[英语]',SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END) AS'[语文]'FROM[TestRows2Columns]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列DECLARE@sql VARCHAR(8000)SET@sql='SELECT [UserName],'SELECT@sql=@sql+'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','FROM (SELECT DISTINCT[Subject]FROM[TestRows2Columns]) AS aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@sql_where NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'SET@sql_where='WHERE UserName = ''王五'''--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM['+@tableName+']'+@sql_where+') p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图6）。

sqlserver列转行函数

sqlserver列转行函数
有多种方法可以将sql server中列数据转换为行数据，具体方法如下：
1、使用UNPIVOT操作：
UNPIVOT函数可以将一行多列数据转换为多行单列数据。
例如：
SELECT id, value
FROM tab_name
UNPIVOT (value for cols1,cols2 in (col1,col2)) unpvt;

在这里，unpvt是UNPIVOT运算符创建的一个虚拟表，cols1和cols2分别表
示两个需要被解析的列，value表示被解析出来的列值。

2、使用CROSS APPLY操作：
CROSS APPLY可以潜在的创建一个表，它可以接受一个结果集作为参数，然
后返回该结果集的列和行。
例如：
SELECT col_value
FROM tab_name
CROSS APPLY (VALUES(Cols1),Cols2) v (col_value);
这段代码中，col_value表示两列数据Cols1和Cols2被转换成的行数据。

sqlserver列转行最简单的方法

sqlserver列转行最简单的方法SQL Server是一种常用的关系型数据库管理系统，它提供了许多强大的功能和工具，可以实现数据的高效存储和管理。

在SQL Server 中，有时候我们需要将列转换为行，这在某些情况下非常有用。

本文将介绍SQL Server中最简单的方法来实现列转行操作。

在SQL Server中，可以使用UNPIVOT关键字来实现列转行的功能。

UNPIVOT关键字用于将多个列合并成一个列，并将每个合并后的列与原始表中的其他列一起输出。

UNPIVOT关键字的语法如下：```SELECT 列名, 值FROM 表名UNPIVOT (值 FOR 列名 IN (列1, 列2, ...)) AS 别名```其中，列名是要输出的列的名称，值是列名对应的值，表名是要进行列转行操作的表的名称，列1、列2等是要转换的列的名称，别名是输出结果的别名。

下面我们通过一个示例来演示如何使用UNPIVOT关键字实现列转行操作。

假设我们有一个表名为students的表，包含了学生的姓名、语文成绩、数学成绩和英语成绩，如下所示：```CREATE TABLE students (姓名 VARCHAR(50),语文成绩 INT,数学成绩 INT,英语成绩 INT);INSERT INTO students (姓名, 语文成绩, 数学成绩, 英语成绩) VALUES ('张三', 85, 90, 95),('李四', 90, 95, 80),('王五', 80, 85, 90);```现在我们需要将该表转换为列名为科目、值为成绩的形式。

我们可以使用以下SQL语句实现：```SELECT 姓名, 科目, 成绩FROM studentsUNPIVOT (成绩 FOR 科目 IN (语文成绩, 数学成绩, 英语成绩)) ASu;```执行上述SQL语句后，将得到以下结果：```姓名科目成绩-----------------张三语文成绩 85张三数学成绩 90张三英语成绩 95李四语文成绩 90李四数学成绩 95李四英语成绩 80王五语文成绩 80王五数学成绩 85王五英语成绩 90```从结果中可以看出，原始表中的每一行被转换为了多行，每行包含了姓名、科目和成绩信息。

pivote函数的使用

SQLServer2005 Pivot 转置使用动态列（应用到视图）SQLServer2005 Pivot 转置使用动态列（应用到视图）最近项目中用到Pivot 对表进行转置，遇到一些问题，主要是Pivot 转置的时候没有办法动态产生转置列名,而作视图的时候又很需要动态的产生这些列，百度上似乎也没有找的很满意的答案，在google上搜到一老外的解决方案，现在自己总结了一下，希望给用的上的朋友一些帮助。

1.创建表脚本if exists(select 1from sysobjectswhere id =object_id('Insurances')and type='U')drop table Insurancesgo/*==============================================================*//* Table: Insurances *//*==============================================================*/ create table Insurances (RefID uniqueidentifier not null,HRMS nvarchar(20)null,Name nvarchar(20)null,InsuranceMoney money null,InsuranceName nvarchar(100)not null, constraint PK_INSURANCES primary key(RefID))go2.测试数据脚本insert into Insurances values(newid(),1,'张三',200,'养老保险')insert into Insurances values(newid(),1,'张三',300,'医疗保险')insert into Insurances values(newid(),2,'李四',250,'养老保险')insert into Insurances values(newid(),2,'李四',350,'医疗保险')insert into Insurances values(newid(),3,'王二',150,'养老保险')insert into Insurances values(newid(),3,'王二',300,'医疗保险')3.查询表数据select HRMS,Name,InsuranceMoney,InsuranceName From InsurancesHRMS Name InsuranceMoney InsuranceName -------------------- -------------------- --------------------- ----------1 张三 200.00 养老保险2 李四 350.00 医疗保险2 李四 250.00 养老保险1 张三 300.00 医疗保险3 王二 300.00 医疗保险3 王二 150.00 养老保险4.转置表数据select*from(select HRMS,Name,InsuranceMoney,InsuranceName from Insurances ) pPivot(sum(InsuranceMoney)FOR InsuranceName IN( [医疗保险], [养老保险]))as pvtHRMS Name 医疗保险养老保险-------------------- -------------------- --------------------- ---------------------2 李四 350.00 250.003 王二 300.00 150.001 张三 300.00 200.005.偶的问题这个语句中医疗保险、养老保险是SQL语句中写死的，而且Sql2005中这个代码没有办法使用动态的查询结果集5.存储过程解决问题所以如果要动态的完成个脚本，可以先拼出SQL 然后通过exec sp_executesql 执行实现存储过程create procedure InsurancePivotasBeginDECLARE @ColumnNames VARCHAR(3000)SET @ColumnNames=''SELECT@ColumnNames = @ColumnNames +'['+ InsuranceName +'],' FROM(SELECT DISTINCT InsuranceName FROM Insurances) tSET @ColumnNames=LEFT(@ColumnNames,LEN(@ColumnNames)-1)DECLARE @selectSQL NVARCHAR(3000)SET @selectSQL='SELECT HRMS,Name,{0} FROM(SELECT HRMS,Name,InsuranceMoney,InsuranceName FROM Insurances ) pPivot( Max(InsuranceMoney) For InsuranceName in ({0})) AS pvtORDER BY HRMS'SET @selectSQL=REPLACE(@selectSQL,'{0}',@ColumnNames)exec sp_executesql @selectSQLend测试存储过程：exec InsurancePivotHRMS Name 养老保险医疗保险-------------------- -------------------- --------------------- ---------------------1 张三 200.00 300.002 李四 250.00 350.003 王二 150.00 300.006.关于视图的新问题和解决方案在视图中没有办法直接调用这个存储过程，但是我们在做程序、做报表的时候又非常需要其实可以通过OPENQUERY来实现（这是一个非正规的解决方式，但目前可以实现）（另外可以使用OPENROWSET,但是参数太多偶放弃了）使用OPENQUERY 的格式是：OPENQUERY([链接服务器],’sql语句’)因为是当前数据的视图，链接服务器可以通过属性查看，MSCBF107 是我测试的链接服务器也可以通过sp_helpserver 查看下面这句话也非常重要，使用的朋友替换[MSCBF107]就ok了，否则使用OPENQUERY会出现未将服务器'MSCBF107' 配置为用于DATA ACCESSsp_serveroption [MSCBF107], 'Data Access', 'True'创建视图如下：create view InsurancePivotViewasselect*From OPENQUERY([MSCBF107],N'SET FMTONLY OFF;exec test.dbo.InsurancePivot')测试视图就可以得到想要的结果了select*from InsurancePivotViewThat’s all。

sql 的行列转换

sql 的行列转换在 SQL 中进行行列转换是一种常见的数据操作，它可以将数据从行的形式转换为列的形式，或者从列的形式转换为行的形式。

下面是两种常见的行列转换方法：1. 使用 `PIVOT` 语句进行行列转换：`PIVOT` 是 SQL 中专门用于进行行列转换的语句。

它允许你将一个表中的行数据按照特定的列值进行分组，并将其他列的值转换为新的列。

下面是一个简单的示例，假设有一个名为 `sales` 的表，包含以下列：`product_id`、`category` 和 `quantity`。

```sqlSELECT category, SUM(quantity) AS total_quantityFROM salesGROUP BY category;```上述示例使用 `GROUP BY` 子句按照 `category` 列进行分组，并使用 `SUM` 函数计算每个分组的 `quantity` 列的总和。

2. 使用 `UNION ALL` 和子查询进行行列转换：有时候，你可能无法直接使用 `PIVOT` 语句进行行列转换，或者你需要更复杂的转换逻辑。

在这种情况下，可以使用 `UNION ALL` 和子查询来实现。

下面是一个示例，将一个包含员工信息的表转换为按部门和职位分类的交叉表。

```sqlSELECT department, job_title, COUNT(*) AS countFROM employeesGROUP BY department, job_title;SELECT department, 'All Jobs' AS job_title, COUNT(*) AS countFROM employeesGROUP BY department;```上述示例使用了两个子查询，一个按照 `department` 和 `job_title` 进行分组，另一个按照 `department` 进行分组，并将所有职位都归为一个名为 `All Jobs` 的虚拟职位。

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*FROM[TestRows2Columns]（图2：样本数据）(二) 先以静态的方式实现行转列，效果如图3所示：--1：静态拼接行转列SELECT[UserName],SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END) AS'[数学]',SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END) AS'[英语]',SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END) AS'[语文]'FROM[TestRows2Columns]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列DECLARE@sql VARCHAR(8000)SET@sql='SELECT [UserName],'SELECT@sql=@sql+'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','FROM (SELECT DISTINCT[Subject]FROM[TestRows2Columns]) AS aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@sql_where NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'SET@sql_where='WHERE UserName = ''王五'''--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM['+@tableName+']'+@sql_where+') p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图6）。

[MSSQL]PIVOT函数

[MSSQL]PIVOT函数PIVOT在帮助中这样描述滴:可以使⽤ PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另⼀个表。

PIVOT 通过将表达式某⼀列中的唯⼀值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执⾏聚合。

UNPIVOT 与 PIVOT 执⾏相反的操作，将表值表达式的列转换为列值。

简单点理解就是⾏变列,UNPIVOT则是列变⾏,⼀个⼀个看测试⽤的数据及表结构:CREATE TABLE ShoppingCart([Week] INT NOT NULL,[TotalPrice] DECIMAL DEFAULT(0) NOT NULL)INSERT INTO ShoppingCart([Week],[TotalPrice])SELECT 1,10 UNION ALLSELECT 2,20 UNION ALLSELECT 3,30 UNION ALLSELECT 4,40 UNION ALLSELECT 5,50 UNION ALLSELECT 6,60 UNION ALLSELECT 7,70SELECT * FROM ShoppingCart输出结果:来看下PIVOT怎么把⾏变列:SELECT'TotalPrice'AS [Week],[1],[2],[3],[4],[5],[6],[7]FROM ShoppingCart PIVOT(SUM(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T输出结果可以看出来,转换完成了,就这么个功能再看⼀个UNPIVOT函数,与上述功能相反,把列转成⾏我们直接使⽤WITH关键字把上述PIVOT查询当成源表,然后再使⽤UNPIVOT关键把它旋转回原来的模样,SQL脚本及结果如下:WITH P AS (SELECT'TotalPrice'AS [Week],[1],[2],[3],[4],[5],[6],[7]FROM ShoppingCart PIVOT(SUM(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7]))AS T)SELECT[WeekDay] AS [Week],[WeekPrice] AS [TotalPrice]FROM PUNPIVOT([WeekPrice] FOR [WeekDay] IN([1],[2],[3],[4],[5],[6],[7]))AS FOOOK介绍完了,⼤概功能如此,但使⽤起来远不⽌如此,灵活运⽤威⼒⽆穷~下边这个SQL语句,下边⼤段注释部分为前同事的作品,上半部分是我重写后的,可以看到,代码量减少了不少!多提宝贵意见!1:ALTER PROCEDURE [dbo].[Report_ApplyStat]2: @TenantId int3:AS4:BEGIN5:/* 模板表 */6:DECLARE @TEMP TABLE([aType] INT)7: INSERT INTO @TEMP([aType])VALUES(1),(2);8:9:/* 查询条件 */10:WITH CONDITION AS(11:SELECT12: SC.PersonId,13: ISNULL(RE.PhaseId,0) AS [PhaseId],14: DATEDIFF(MONTH,SC.ApplyDate,GETDATE()) AS DIFF15:FROM SearchCV SC16:LEFT JOIN [REL_PersonJobStoreDB] RE ON SC.PersonId = RE.PersonId17:WHERE SC.TenantId = @TenantId),18: [ApplyCount] AS(SELECT 1 AS [TYPE],DATEDIFF(MONTH,ApplyDate,GETDATE()) AS DIFF,(PersonId) AS [ApplyCount] FROM SearchCV WHERE TenantId = @TenantId), 19: [OfferCount] AS(SELECT 2 AS [TYPE],DIFF,(PersonId) AS [OfferCount] FROM CONDITION WHERE PhaseId = 4 ),20: [Result] AS(21:SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [ApplyCount] PIVOT(COUNT([ApplyCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T UNION ALL22:SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [OfferCount] PIVOT(COUNT([OfferCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T )23:SELECT24: TP.[aType] AS [aType],25: ISNULL(RE.[6],0) AS [M1],26: ISNULL(RE.[5],0) AS [M2],27: ISNULL(RE.[4],0) AS [M3],28: ISNULL(RE.[3],0) AS [M4],29: ISNULL(RE.[2],0) AS [M5],30: ISNULL(RE.[1],0) AS [M6]31:FROM @TEMP TP32:LEFT JOIN [Result] RE ON TP.[aType] = RE.[aType] Order by TP.aType33:34: --定义表变量35:36: --declare @TenantId INT37: --select @TenantId=10000138: --DECLARE @temp1 table(39:-- aType INT DEFAULT(0),40:-- mon int,--取今天与⼊库时间的⽉份差,如SELECT DATEDIFF(MONTH,'2010-7-1',GETDATE()) = 241:-- total int42:-- )43: --DECLARE @temp2 table(44:-- aType int NOT NULL default 0,45:-- mon varchar(40),46:-- total int)47: --DECLARE @stat_date DATETIME48: --DEClARE @Index int49: --declare @s varchar(4000), @sql varchar(4000)50: --SET @s = ''51: --SET @Index=152: --SET @stat_date = GETDATE()-15053: ----遍历出要统计的⽉份54: --WHILE month(@stat_date) <= month(GETDATE())55: --BEGIN56:-- Select @s=@s+ 'M'+convert(varchar(20),@Index)+ ' = max(case when mon = '+QUOTENAME(left(CONVERT(varchar,@stat_date,102),7),'''')+ ' then total else 0 end),' 57:-- SET @stat_date=DATEADD(MONTH, 1, @stat_date)58:-- SET @Index=@Index+159:60: --END61: --Select @s= SUBSTRING(@s,0,len(@s))62: --print @s63:64: --应聘总数65: --insert into @temp1(aType,mon,total)66: --select 1,DATEDIFF(MONTH,CreateDate,GETDATE()) AS mon,COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId group by DATEDIFF(MONTH,CreateDate,GETDATE())67: --IF(NOT EXISTS(SELECT 1 FROM @temp1))68: --BEGIN69:-- insert into @temp1(aType,mon,total)70:-- SELECT 1,-1,071: --END72: ----匹配应聘标识号73: --Update @temp1 set aType=174:75: --已录⽤⼈数76: --insert into @temp1(aType,mon,total)77: --select 2,DATEDIFF(MONTH,CreateDate,GETDATE()),COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId and PhaseId = 4 group by DATEDIFF(MONTH,CreateDate,GETDATE()) 78: --IF(NOT EXISTS(SELECT 1 FROM @temp1 WHERE aType=2))79: --BEGIN80:-- insert into @temp1(aType,mon,total)81:-- SELECT 2,-1,082: --END83:84: --SELECT * FROM @temp185: --匹配应聘标识号86: --Update @temp2 set aType=287: ----合并表数据88: --insert into @temp1 select * from @temp289:90: --select * from @temp191:92: --DECLARE @DATE DATETIME93: --SET @DATE = GETDATE()94: --SELECT GETDATE(),DATEADD(MONTH,-1,GETDATE()),DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,1,GETDATE()))95: --select aType,mon,avg(total) total from @temp196:-- group by atype,mon97:98: --select [aType],99: --M1 = max(case when mon = 6 then total else 0 end),100: --M2 = max(case when mon = 5 then total else 0 end),101: --M3 = max(case when mon = 4 then total else 0 end),102: --M4 = max(case when mon = 3 then total else 0 end),103: --M5 = max(case when mon = 2 then total else 0 end),104: --M6 = max(case when mon = 1 then total else 0 end)105: --from106: --(107:-- select aType,mon,avg(total) total from @temp1108:-- group by atype,mon109:-- ) aa110:-- group by [aType]111:112:113:114:-- print @sql115: --exec(@sql)116:117: END原代码即为32⾏以后,新代码为前32⾏,显然重构后减少了Bad Smell猜测您可能对下边的⽂章感兴趣如果您喜欢该博客请点击右下⾓推荐按钮,您的推荐是作者创作的动⼒!。

mysql中pivot和unpivot方法 -回复

mysql中pivot和unpivot方法-回复标题：MySQL中的Pivot和Unpivot方法详解在MySQL中，Pivot和Unpivot是两种强大的数据转换工具，它们可以帮助我们以更直观、更易于理解的方式展示数据。

这两种方法主要用于将行数据转换为列数据（Pivot）或反之（Unpivot）。

本文将详细解析这两种方法的使用步骤和应用场景。

一、Pivot方法Pivot方法，也称为旋转或透视，主要用于将行数据转换为列数据。

这种转换方式可以使数据更易于分析和理解。

以下是一个简单的步骤来演示如何在MySQL中使用Pivot方法：1. 原始数据表假设我们有一个名为sales的数据表，其中包含以下数据：product year sales-A 2018 100B 2018 200A 2019 150B 2019 2502. 使用Pivot方法转换数据我们想要将每年的产品销售额从行数据转换为列数据，可以使用以下SQL 查询：sqlSELECT *FROM (SELECT product, year, salesFROM sales) AS srcPIVOT (SUM(sales)FOR year IN ('2018', '2019')) AS pvt;3. 结果上述查询将返回以下结果：product '2018' '2019'-A 100 150B 200 250在这个例子中，Pivot方法将"year"字段的值转换为了列名，并对每个产品的年度销售额进行了求和。

二、Unpivot方法与Pivot方法相反，Unpivot方法用于将列数据转换为行数据。

这种转换方式有助于将具有多列的数据表转换为更简单的键值对形式。

以下是一个简单的步骤来演示如何在MySQL中使用Unpivot方法：1. 原始数据表假设我们有一个名为product_sales的数据表，其中包含以下数据：product sales_2018 sales_2019A 100 150B 200 2502. 使用Unpivot方法转换数据我们想要将每年的产品销售额从列数据转换为行数据，可以使用以下SQL 查询：sqlSELECT product, year, salesFROM (SELECT product, sales_2018, sales_2019FROM product_sales) AS srcUNION ALLSELECT product, '2018', sales_2018FROM product_salesUNION ALLSELECT product, '2019', sales_2019FROM product_sales;3. 结果上述查询将返回以下结果：product year sales-A 2018 100A 2019 150B 2018 200B 2019 250在这个例子中，Unpivot方法将"sales_2018"和"sales_2019"字段的值转换为了行数据，并创建了一个新的"year"字段。