关于UNPIVOT 操作符

?

UNPIVOT 操作符说明

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

图1

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

UNPIVOT 语法

下面就是 UNPIVOT 的语法:

SELECT [columns not unpivoted],[unpivot_column],[value_column],FROM
()
AS 
UNPIVOT ( [value_column] FOR [unpivot_column] IN (  ) ) 
   AS 

Where:

• [columns not unpivoted]: 不被转换的列的名字清单。
• [unpivot_column]: 不转换的列的名称。
• [value_column]: 确定一个列名称来代表不转换的列的数据。
• : 源数据。
• ????? : 为源数据转换后的表确定一个别名。
• ????? :? 被转换的列的列名称。
• ???? : 为转换操作的整套生产，确定一个别名。

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

简单的例子

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),(2,'444-555-1950',(3,'444-555-1324','444-555-2310'),(4,'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,WorkPhone,FaxNumber)) AS UNPVT;

Listing 2: 行列转换语法例子

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

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

使用两个UNPIVOT操作符

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

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

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

通过这个输出结果，能发现不同的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,'')
-- 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(''),'')
-- Generate UNPIVOT Statement
DECLARE @CMD nvarchar(2000);
SET @CMD = 'SELECT CustId,CustName,PrefType,PrefValue FROM ' + 
           '(SELECT CustID,' + @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,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 的成对的列。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!