关于UNPIVOT 操作符

关于UNPIVOT 操作符

 

UNPIVOT 操作符说明

简而言之,UNPIVOT操作符就是取得一个行的数据集合,然后把每一行都转换成多个行数据。为了更好地理解,请看下图:

UNPIVOT Operation

图1

从上图中,你能发现UNPOVOT操作符,取得了两行数据,每行包含三个Price值,然后将这些转化成6行数据,其中每个产品价格都是一个不同的行。

UNPIVOT 命令制定了两个不同的列类型。第一个类型是列中不被转换的。在例子中,ID、产品名字列是这样的列类型。第二种列类型就是那些被转换的。诸如ProductCode, Wholesale 和Retail 这三列。在我上面的例子中,那些没有被转换的列将被在每套列值集合中重复,而另外的那些列将被转换成行。

UNPIVOT 语法

下面就是 UNPIVOT 的语法:

SELECT [columns not unpivoted],
	 [unpivot_column],
       [value_column],
FROM
(<source query>)
AS <alias for the source data>
UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) ) 
   AS <alias for unpivot>

Where:

    • [columns not unpivoted]: 不被转换的列的名字清单。
    • [unpivot_column]: 不转换的列的名称。
    • [value_column]: 确定一个列名称来代表不转换的列的数据。
    • <source query>: 源数据。
  •       <alias for the source data>: 为源数据转换后的表确定一个别名。
  •       <column_list>:  被转换的列的列名称。
  •      <alias for unpivot>: 为转换操作的整套生产,确定一个别名。

为了更好地理解我们看下面的例子:

简单的例子

USE tempdb;
GO
IF object_id('PhoneNumbers') IS NOT NULL DROP TABLE PhoneNumbers;
GO
CREATE TABLE PhoneNumbers (
	PersonID int, 
	HomePhone varchar(12),
	CellPhone varchar(12), 
	Workphone varchar(12), 
	FaxNumber varchar(12));

INSERT INTO PhoneNumbers VALUES 
	(1,Null,'444-555-2931',Null,Null),
	(2,'444-555-1950','444-555-2931',Null, Null),
	(3,'444-555-1950', Null,'444-555-1324','444-555-2310'),
	(4,'444-555-1950','444-555-2931','444-555-1324',
        '444-555-1987');

Listing 1: 创建并填充PhoneNumbers 数据

SELECT PersonID, PhoneType, PhoneNumber
FROM (
	SELECT PersonID, HomePhone, CellPhone, Workphone, FaxNumber
	FROM PhoneNumbers ) AS Src
	UNPIVOT (
		PhoneNumber FOR PhoneType IN 
		(HomePhone, CellPhone, WorkPhone, FaxNumber)) AS UNPVT;

Listing 2: 行列转换语法例子

执行上面代码后显示如下图:

unpivot

通过这个例子,我们发现执行结果中每行数据只包含一个单一的电话号码,同时注意到结果中在原表中有几个号码不为null则有几行数据,ID也就有几次。接下来我们进一步通过使用UNPIVOT来加深认识。

使用两个UNPIVOT操作符

第二个例子中,我将使用两个操作符来行转列来转换一套名字/值 的两列数据。具体如下:

unpivot1

 

在表 CustPref里面 我有四对名称和值。

我们将使用不同的UNPIVOT操作符来创建一个结果集,每一个PrefType的名字和值针对每个CustID 和CustName。并联使用操作符的作用是为了转换两组列。这样讲能表示为一个参数名称和值在一行里面。执行代码如下:

3http://www.cnblogs.com/wenBlog/

通过这个输出结果,能发现不同的type对应不同的值得列,并且要关联CustID。整个查询通过两个不同的UNPOVOT操作符同时使用了where 子句来合并输出结果(基于列名前五个字符相同的进行匹配),第一个行转列转换的是数据,第二个为类型,where限制了比较前五个字符,我能取得匹配的数据组。

动态UNPIVOT查询

代码如下:

USE tempdb;
GO
DECLARE @ColNames varchar(1000);
SET @ColNames = '';
-- Get PrefValue Columns
SELECT @ColNames=stuff((
    SELECT DISTINCT ',' + QUOTENAME(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME = 'CustPref'
	  AND COLUMN_NAME like 'Pref_Type'
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')
-- Get PrefType Columns
DECLARE @ColValues varchar(1000);
SET @ColValues = '';
SELECT @ColValues=stuff((
    SELECT DISTINCT ',' + QUOTENAME(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME = 'CustPref'
	  AND COLUMN_NAME like 'Pref_Data'
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')
-- Generate UNPIVOT Statement
DECLARE @CMD nvarchar(2000);
SET @CMD = 'SELECT CustId, CustName, PrefType, PrefValue FROM ' + 
           '(SELECT CustID, CustName, ' + @ColNames + ',' + @ColValues + 
		   ' FROM CustPref) AS Perf UNPIVOT (PrefValue FOR PrefValues IN (' +  
		   @ColValues + ')) AS UP1 UNPIVOT (PrefType FOR PrefTypes IN (' + 
		   @ColNames + ')) AS UP2 WHERE ' + 
		   'substring(PrefValues,5,1) = substring(PrefTypes,5,1);'
-- Print UNPIVOT Command
PRINT @CMD
-- Execute UNPIVOT Command
execute sp_executesql @CMD

结果是与上面的例子完全相同的。

为了完成和这个动态的SQL,我使用了INFORMATION_SCHEMA.COLUMNS视图。这个视图能帮我们设定两个变量@ColNames和@ColValues ,这就包含了用逗号区分的列名的字符串。这两个变量被用来构建动态的行转列查询。一旦我建立了动态的SQL就能,执行这个sp_executesql了。

这是一个简单的实例,但是相同的逻辑可以应用于更多的不同的组列的转换。

Summary

UNPIVOT操作符在2005 首次被引入,允许我们将多个name/value 列从不规范的表中创建到一个规范画的结果集中,并且一一对应于选定的列。通过使用这个操作符,我们能同时转换多个不同组的name/value 的成对的列。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119738.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 电商项目中的SPU和SKU概念

    电商项目中的SPU和SKU概念SPUSPU:StandardProductUnit,标准产品单位。概念:SPU是商品信息聚合的最小单位,是一组可复用、易检索的标准化信息的集合,该集合描述了一个产品的特性。通俗点讲,属性值、特性相同的货品就可以称为一个SPUSPU是用来定位的例如:iphone8就是一个SPU,与商家、颜色、款式、套餐都无关SKUSKU:StockKeepingUnit,库存量单…

    2022年10月20日
  • 2019最新版Eclipse下载与安装

    2019最新版Eclipse下载与安装首先:eclispe提供了两种下载方式。一种是下载安装程序,下载后直接运行安装程序,根据安装程序的提示选择安装选项并完成安装;一种是下载eclispe压缩包,下载后手动解压即可。下面分别介绍两种安装方式。一、首先进入Eclipse下载页面1.下载地址:https://www.eclipse.org/downloads/二、安装程序的方式如果是64位的Windows操作系统,可以直接单击图…

  • 动态规划-背包问题

    动态规划-背包问题背包问题是一种组合优化的NP完全问题。有N个物品和容量为W的背包,每个物品都有自己的体积w和价值v,求拿哪些物品可以使得背包所装下的物品的总价值最大。如果限定每种物品只能选择0个或1个,则问题称为0-1背包问题;如果不限定wu’pi…

  • wxpython中文教程_wxPython中文教程 简单入门加实例

    wxpython中文教程_wxPython中文教程 简单入门加实例原博文2015-04-2906:56−wx.Window是一个基类,许多构件从它继承。包括wx.Frame构件。技术上这意味着,我们可以在所有的子类中使用wx.Window的方法。我们这里介绍它的几种方法:*SetTitle(stringtitle)——设置窗口标题。只可用…相关推荐2008-11-2312:00−一、简介wxPython是Pytho…

  • mssql datetime smalldatetime 字段类型插入代码「建议收藏」

    mssql datetime smalldatetime 字段类型插入代码「建议收藏」
    如果mssql字段类型是datetime ,插入代码如下
                   ps=conn.prepareStatement(strSQL2);
                   ps.setString(1,(newjava.util.Date()).toLocaleString());  
                   ps.executeUpdate();
     
    如果mssql字段类型

  • 如何创建HTML表单?html表单代码怎么写[通俗易懂]

    如何创建HTML表单?html表单代码怎么写[通俗易懂]html表单代码是什么?如何创建HTML表单?这些对于新手会感到陌生,下面我们为你总结一下html表单代码怎么写?以及html表单的创建?一:构建表单标签在文本编辑器中打开HTML文档,必须在<form>和</form>标签中键入HTML表单的内容。这些标签充当表单的容器,就像<div></div>容器标签一样。您可以在<form></form>标签内使用CSS或js,使您的表单看起来比较美观。2.打开<form&gt

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号