TSQL 表转换字段 =>列 [英] TSQL Table Transformation Fields => Columns

问题描述

我有以下表格布局.每个行值将始终是唯一的.永远不会有多个相同 Id、Name 和 Line 的实例.

I have the following table layout. Each line value will always be unique. There will never be more than one instance of the same Id, Name, and Line.

Id Name Line
1  A    Z
2  B    Y
3  C    X
3  C    W
4  D    W

我想查询数据，以便 Line 字段成为一列.如果该值存在，则在字段数据中应用 1，否则应用 0.例如

I would like to query the data so that the Line field becomes a column. If the value exists, a 1 is applied in the field data, otherwise a 0. e.g.

Id Name Z Y X W
1  A    1 0 0 0
2  B    0 1 0 0
3  C    0 0 1 1
4  D    0 0 0 1

字段名称 W、X、Y、Z 只是字段值的示例，因此我无法应用运算符来显式检查，例如X"、Y"或Z".这些可以随时更改，并且不限于一组特定的值.结果集中的列名应将唯一字段值反映为列.

The field names W, X, Y, Z are just examples of field values, so I can't apply an operator to explicitly check, for example, 'X', 'Y', or 'Z'. These could change at any time and are not restricted to a finate set of values. The column names in the result-set should reflect the unique field values as columns.

知道我如何才能做到这一点吗?

Any idea how I can accomplish this?

推荐答案

这是一个标准的透视查询.

It's a standard pivot query.

如果 1 代表一个布尔指标 - 使用:

If 1 represents a boolean indicator - use:

  SELECT t.id,
         t.name,
         MAX(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         MAX(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         MAX(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         MAX(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

如果 1 代表组中具有该值的记录数，请使用:

If 1 represents the number of records with that value for the group, use:

  SELECT t.id,
         t.name,
         SUM(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         SUM(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         SUM(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         SUM(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

这篇关于TSQL 表转换字段 =>列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！