您是否正在使用SQL PIVOT？ 你应该！

有时，我们会遇到这些罕见的SQL问题，在这些问题中我们想做一些看起来与众不同的事情。 这些事情之一就是将行旋转到列。

瓦利安特（Valiante） 最近对堆栈溢出问题提出了确切的要求。 从此表：

+------+------------+----------------+-------------------+
| dnId | propNameId |  propertyName  |   propertyValue   |
+------+------------+----------------+-------------------+
|    1 |         10 | objectsid      | S-1-5-32-548      |
|    1 |         19 | _objectclass   | group             |
|    1 |         80 | cn             | Account Operators |
|    1 |         82 | samaccountname | Account Operators |
|    1 |         85 | name           | Account Operators |
|    2 |         10 | objectsid      | S-1-5-32-544      |
|    2 |         19 | _objectclass   | group             |
|    2 |         80 | cn             | Administrators    |
|    2 |         82 | samaccountname | Administrators    |
|    2 |         85 | name           | Administrators    |
|    3 |         10 | objectsid      | S-1-5-32-551      |
|    3 |         19 | _objectclass   | group             |
|    3 |         80 | cn             | Backup Operators  |
|    3 |         82 | samaccountname | Backup Operators  |
|    3 |         85 | name           | Backup Operators  |
+------+------------+----------------+-------------------+

…我们想这样将行转换成列：

+------+--------------+--------------+-------------------+-------------------+-------------------+
| dnId |  objectsid   | _objectclass |        cn         |  samaccountname   |       name        |
+------+--------------+--------------+-------------------+-------------------+-------------------+
|    1 | S-1-5-32-548 | group        | Account Operators | Account Operators | Account Operators |
|    2 | S-1-5-32-544 | group        | Administrators    | Administrators    | Administrators    |
|    3 | S-1-5-32-551 | group        | Backup Operators  | Backup Operators  | Backup Operators  |
+------+--------------+--------------+-------------------+-------------------+-------------------+

这个想法是，我们只希望每个不同的dnId ，然后我们想要将属性名-值对转换为列，每个属性名一行。

使用Oracle或SQL Server PIVOT

使用Oracle和SQL Server ，上述转换实际上非常容易，它们都支持表表达式上的PIVOT关键字。

这是使用SQL Server产生所需结果的方式：

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) AS t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    objectsid, 
    _objectclass, 
    cn, 
    samaccountname, 
    name
  )
) AS p;

（SQLFiddle在这里）

同样的查询在Oracle中的语法略有不同：

SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid", 
    '_objectclass'   as "_objectclass", 
    'cn'             as "cn", 
    'samaccountname' as "samaccountname", 
    'name'           as "name"
  )
) p;

（SQLFiddle在这里）

它是如何工作的？

重要的是要理解， PIVOT （非常类似于JOIN ）是一个关键字，该关键字被应用于表引用以对其进行转换。 在上面的示例中，我们实质上是在对派生表t进行转换以形成数据透视表p 。 我们可以更进一步，将p到另一个派生表，如下所示：

SELECT *
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid", 
    '_objectclass'   as "_objectclass", 
    'cn'             as "cn", 
    'samaccountname' as "samaccountname", 
    'name'           as "name"
  )
) p
JOIN (
  SELECT dnId, COUNT(*) availableAttributes
  FROM myTable
  GROUP BY dnId
) q USING (dnId);

上面的查询现在将允许查找在每一列中没有名称/值对的那些行。 假设我们从原始表中删除了一项，那么上面的查询现在可能会返回：

| DNID |    OBJECTSID | _OBJECTCLASS |                CN |    SAMACCOUNTNAME |              NAME | AVAILABLEATTRIBUTES |
|------|--------------|--------------|-------------------|-------------------|-------------------|---------------------|
|    1 | S-1-5-32-548 |        group | Account Operators | Account Operators | Account Operators |                   5 |
|    2 | S-1-5-32-544 |        group |    Administrators |            (null) |    Administrators |                   4 |
|    3 | S-1-5-32-551 |        group |  Backup Operators |  Backup Operators |  Backup Operators |                   5 |

jOOQ还通过其API支持SQL PIVOT子句 。

如果我没有PIVOT怎么办？

在简单的PIVOT方案中，Oracle或SQL Server以外的其他数据库的用户可以编写一个等效查询，该查询使用GROUP BY和MAX(CASE ...)表达式，如此处答案所示 。

翻译自: https://www.javacodegeeks.com/2014/08/are-you-using-sql-pivot-yet-you-should.html