sql-server - 多个枢轴
问题描述
我有一个如下所示的表:
+---------+----------+---------+-------+-----------+-----------+-------------+
| 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 |
+---------+----------+---------+-------+-----------+-----------+-------------+
小提琴: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 |
+----------+-------+-------+-------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+-----------------+-----------------+-------------------+
小提琴:http ://www.sqlfiddle.com/#!9/6e84ff2/2/0
我了解如何进行数据透视以将“值”作为字段,但不了解如何包含多个数据透视并将它们命名为 Field + _ + OriginalColumn
编辑: Field 的不同值的数量众所周知,可以硬编码到答案中。
解决方案
因为这些字段知道您可以使用条件聚合函数
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 功能。请注意 value 列只能包含一种数据类型,因此我将 boolean 转换为 0/1
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
;
推荐阅读
- excel - 当我想要部分匹配时使用 =IF(OR 公式
- javascript - How to convert a date in specific timezone to utc
- laravel - Laravel 数据验证的问题
- python - 如何抓取网站“检查”元素中“网络”选项卡中显示的文件的响应?
- sql - 自动插入
- browser - 为什么浏览器不能完全通过 API 控制来自 JavaScript 的 HTTP2 帧?
- java - 已编译的 jar 文件中的 Java 资源路径
- reactjs - 如何检测 Next.js 中 URL 哈希的变化?
- linux - 运行 git clone 时出现错误:errno 10054
- django - Django + Angular = 全局变量