UNPIVOT 操作符说明
简而言之,UNPIVOT操作符就是取得一个行的数据集合,然后把每一行都转换成多个行数据。为了更好地理解,请看下图:
图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: 行列转换语法例子
执行上面代码后显示如下图:
通过这个例子,我们发现执行结果中每行数据只包含一个单一的电话号码,同时注意到结果中在原表中有几个号码不为null则有几行数据,ID也就有几次。接下来我们进一步通过使用UNPIVOT来加深认识。
使用两个UNPIVOT操作符
第二个例子中,我将使用两个操作符来行转列来转换一套名字/值 的两列数据。具体如下:
在表 CustPref里面 我有四对名称和值。
我们将使用不同的UNPIVOT操作符来创建一个结果集,每一个PrefType的名字和值针对每个CustID 和CustName。并联使用操作符的作用是为了转换两组列。这样讲能表示为一个参数名称和值在一行里面。执行代码如下:
http://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账号...