将行的值转换为列

Question

我在表中有一个列，它可以有3个值--高、中、低。

我想在一个网页上显示这些与3个复选框(高，中，低)为每个记录在表中，与相应的复选框勾选根据优先级。

ID  Priority
-----------
1   High
2   Low
3   High
4   Medium

我想编写一个返回以下输出的SQL查询

ID  High  Medium  Low
---------------------
1    Y
2                  Y
3    Y
4           Y

我想的方法是：

select 
    ID, 
    case 
       when priority = "High" then "Y" 
       else "N" 
    end as High,
    case 
       when priority = "Medium" then "Y" 
       else "N" 
    end as Medium,
    case 
       when priority = "Low" then "Y" 
       else "N" 
    end as Low;

这是我真正想要达到的玩具问题。

在我的实际项目中，我有5-6个这样的选项，每个列都不同，而且有4个这样的列使用这种方法使我的查询非常长。

下面是一些列和它们可能具有的各种值的示例

• 业务增长-增长，稳定，下降，NA
• 检查的频率

请就更简单、更有效的解决方案提出建议。

Answer 1

你可以这样做：

SELECT id, 
       CASE WHEN priority = 'High' THEN 'Y' ELSE '' END AS High,
       CASE WHEN priority = 'Medium' THEN 'Y'  ELSE '' END AS Medium,
       CASE WHEN priority = 'Low' THEN 'Y'  ELSE '' END AS Low
FROM table

Answer 2

DECLARE @T TABLE ( ID INT, Priority NVARCHAR(50) );

INSERT INTO @T ( ID,Priority) VALUES
(1,   N'High'),
(2,   N'Low'),
(3,   N'High'),
(4,   N'Medium');

SELECT ID, ISNULL(High, F) As High, ISNULL(Medium, F) As Medium, ISNULL(Low, F) As Low
FROM
    (
        SELECT  ID, Priority, 'Y' T , '' F
        FROM @T
     ) P1
     PIVOT
     ( 
         MAX(T) for Priority IN ([High], [Medium], [Low])
      )
      P2 ORDER BY ID;

结果：

+----+------+--------+-----+
| ID | High | Medium | Low |
+----+------+--------+-----+
|  1 | Y    |        |     |
|  2 |      |        | Y   |
|  3 | Y    |        |     |
|  4 |      | Y      |     |
+----+------+--------+-----+

或将IIF()用作：

SELECT ID, 
       IIF(Priority = 'High', 'Y', '') AS High,
       IIF(Priority = 'Medium', 'Y', '') AS Medium,
       IIF(Priority = 'Low', 'Y', '') AS Low
FROM @T;

Answer 3

你给我看的密码有效。你有什么问题？

select id
       ,case when priority = 'high' then 'y' else 'n' end as high
       ,case when priority = 'medium' then 'y' else 'n' end as medium
       ,case when priority = 'low' then 'y' else 'n' end as low
from mytable

下面是一个可以使用这里的rextester示例。