将行的值转换为列

I have a column in a table which can have 3 values - High, Medium , Low.

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

I want to display these on a web page with 3 checkboxes (High, Medium, Low) for each record in table, with the corresponding checkbox ticked based on the priority.

我希望在一个网页上显示这些内容，其中包含表格中每条记录的3个复选框（高，中，低），并根据优先级勾选相应的复选框。

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

I would like to write a SQL query that returns the following output

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

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

The approach I thought about is:

我想到的方法是：

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;

This is a toy problem of what I am actually trying to achieve.

这是我实际想要实现的玩具问题。

In my real project, I have 5-6 of such options which are different for each column and there are 4 such columns using this approach is making my query very long.

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

Here is a example of some columns and various values they might have

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

Business growth - Growing , Stable , Decline , NA
业务增长 - 增长，稳定，下降，NA
Frequency of Checks - Often , Sometimes , Never , NA
检查频率 - 通常，有时，从不，NA

Please advice on a simpler more efficient solution.

请建议更简单，更有效的解决方案。

4 个解决方案

#1

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;

Result:

结果：

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

Or using IIF() as:

或者使用IIF（）作为：

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

#2

You can do something like this:

你可以这样做：

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

#3

The code you showed me works. What issues are you having?

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

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

Here is a rextester example you can play with here.

这是一个你可以在这里玩的rextester例子。

#4

You can use CASE statement or computed columns.

您可以使用CASE语句或计算列。

#1

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;