多重数据透视表

Question

我有一个表格，长这样：

+---------+----------+---------+-------+-----------+-----------+-------------+
| ValueId | ObjectId |  Field  | Value | Estimated | OrigValue | FromDefault |
+---------+----------+---------+-------+-----------+-----------+-------------+
|       1 |        1 | 'Stat1' | 35    | true      | (null)    | (null)      |
|       2 |        1 | 'Stat2' | 2     | false     | 0         | true        |
|       3 |        1 | 'Stat3' | 0.213 | true      | 0.212     | false       |
|       4 |        2 | 'Stat1' | 513   | true      | 122       | true        |
|       5 |        2 | 'Stat2' | 31    | true      | (null)    | true        |
|       6 |        2 | 'Stat3' | 2.411 | true      | (null)    | false       |
+---------+----------+---------+-------+-----------+-----------+-------------+

Fiddle: http://www.sqlfiddle.com/#!9/445271/2/0

我希望透视表看起来像这样：

+----------+-------+-------+-------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+
| ObjectId | Stat1 | Stat2 | Stat3 | Stat1_Estimated | Stat1_OrigValue | Stat1_FromDefault | Stat2_Estimated | Stat2_OrigValue | Stat2_FromDefault | Stat3_Estimated | Stat3_OrigValue | Stat3_FromDefault |
+----------+-------+-------+-------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+
|        1 |    35 |     2 | 0.213 | true            | (null)          | (null)            | false           | false           | true              | true            | 0.212           | false             |
|        2 |   513 |    31 | 2.411 | true            | 122             | true              | true            | (null)          | true              | true            | (null)          | false             |
+----------+-------+-------+-------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+

Fiddle: http://www.sqlfiddle.com/#!9/6e84ff2/2/0

我理解如何进行数据透视来将“value”作为字段，但不知道如何包含多个数据透视并将它们命名为 Field + _ + OriginalColumn。

编辑：Field 的不同值的数量是众所周知的，可以硬编码到答案中。

- CuriousDeveloper

---

你能否也有'stat4'？ 'stat5'，'stat6'，'stat7'等呢？总的来说，最好在演示层进行旋转操作，而不是在数据引擎中进行。 - Thom A

不需要动态 SQL（据我所知），因为统计数据的数量是众所周知的。 - CuriousDeveloper

你想要看到什么结果并不是很清楚。您说Field + _ + OriginalColumn，那看起来会是什么样子呢？例如，“Stat1”是否像“Field_Stat1”这样？此外，“多个数据透视表”具体是什么意思？ - ccarpenter32

那么问题是什么？这里有很多针对SQL Server的数据透视表示例：https://stackoverflow.com/search?q=sql+server+pivot - Juan Carlos Oropeza

Answer 1

SQL DEMO

由于字段已知，您可以使用条件聚合函数。

SELECT ObjectId,
       MAX(CASE WHEN Field = '''Stat1''' THEN Value END)  as Stat1,
       MAX(CASE WHEN Field = '''Stat2''' THEN Value END)  as Stat2,
       MAX(CASE WHEN Field = '''Stat3''' THEN Value END)  as Stat3,
       MAX(CASE WHEN Field = '''Stat1''' THEN Estimated END)  as Stat_Estimated1,
       MAX(CASE WHEN Field = '''Stat1''' THEN OrigValue END)  as Stat_OrigValue1,
       MAX(CASE WHEN Field = '''Stat1''' THEN FromDefault END)  as Stat_FromDefault1,
       MAX(CASE WHEN Field = '''Stat2''' THEN Estimated END)  as Stat_Estimated2,
       MAX(CASE WHEN Field = '''Stat2''' THEN OrigValue END)  as Stat_OrigValue2,
       MAX(CASE WHEN Field = '''Stat2''' THEN FromDefault END)  as Stat_FromDefault2,
       MAX(CASE WHEN Field = '''Stat3''' THEN Estimated END)  as Stat_Estimated3,
       MAX(CASE WHEN Field = '''Stat3''' THEN OrigValue END)  as Stat_OrigValue3,
       MAX(CASE WHEN Field = '''Stat3''' THEN FromDefault END)  as Stat_FromDefault3

FROM mytable
GROUP BY ObjectId
;

您也可以排列数据并使用PIVOT函数。请注意，值列只能包含一个数据类型，因此我将布尔值转换为0/1。

SQL DEMO

SELECT ObjectId, Field, Value
FROM myTable

UNION ALL

SELECT ObjectId, CONCAT(Field, '_Estimated'), CASE WHEN Estimated IS NULL THEN NULL
                                                   WHEN Estimated THEN 1
                                                   ELSE 0 
                                              END
FROM myTable

UNION ALL

SELECT ObjectId, CONCAT(Field, '_OrigValue'), OrigValue
FROM myTable

UNION ALL

SELECT ObjectId, CONCAT(Field, '_FromDefault'), CASE WHEN FromDefault IS NULL THEN NULL
                                                     WHEN FromDefault THEN 1
                                                     ELSE 0 
                                                END
FROM myTable
;

- Juan Carlos Oropeza