sql - 透视这些数据的好方法是什么？

Question

我有以下粒度的数据:

CityID | Name | Post_Science | Post_Science | Post_Reading | Pre_Reading | Post_Writing | Pre_Writing  
123    | Bob  | 2.0          | 1.0          |     2.0      |    4.0      |     1.0      | 1.0

我会调用这些<Post/Pre>_XXXXXX列作为标签。基本上，这些不带“Pre”或“Post”文本的列名称映射到另一个表中的标签。

我想以某种方式旋转数据，以便 pre和post对于每组 CityID, Name, Label ，相同标签的值位于同一行。所以它看起来像这样:

CityID | Name | Pre Category | Post Category | Label 
123    | Bob  | 1.0          |     2.0       | Science
123    | Bob  | 4.0          |     2.0       | Reading
123    | Bob  | 1.0          |     1.0       | Writing

Label通过连接来自一个单独的表。希望这不会让任何人感到困惑。如果是，请暂时忽略该列。

所以这些类别还有很多——科学、阅读和写作只是我选择的几个类别。

我想到了两种获取这种格式数据的选项:

1. 将所有数据逆透视为 CityID, Name, Label 组中所有值的长列表。 。然后解析标签名称并将一个类别的前值和后值转回到 1 行
2. 做一堆Union s。因此，将所有科学合而为一select声明，全部Reading在另一个 select 语句中并将它们联合起来。大约有 50 个配对，因此有 50 个 union 语句

我想第一个选项比后者更干净。还有其他选择吗？

Answer 1

这是不旋转，我强烈建议应用:

select t.CityId, t.Name, v.*
from t cross apply
     (values (t.Post_Science, t.Pre_Science, 'Science'),
             (t.Post_Reading, t.Pre_Reading, 'Reading'),
             (t.Post_Writing, t.Pre_Writing, 'Writing')
     ) v(postcategory, precategory, label) ;

UNPIVOT 是做一件事的非常特殊的语法。 APPLY 引入了横向连接，这对于此目的和许多其他目的非常强大。