Convert values of rows into columns

Question

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

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.

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

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

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.

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

Business growth - Growing , Stable , Decline , NA
Frequency of Checks - Often , Sometimes , Never , NA

Please advice on a simpler more efficient solution.

I tried to do it using case statement but I realised that it depends on the number of options I have and querry is getting very long. — ashish2199, Oct 30 '17 at 17:22
What have you tried? Try look into using a `case` statement here — Simon, Oct 30 '17 at 17:22
@ashish2199 That's correct, and it looks like you only have 3 options? so it's just a `case when priority = 'high' then priority else null end` `case when medium then... case when low then...` — Simon, Oct 30 '17 at 17:25
Post the code you have tried, even if it errors or doesn't bring you the desired results. — Simon, Oct 30 '17 at 17:26
Can you show a data sample closer to what you are trying to accomplish with your project? Such as it reflecting your 5-6 option with the 4 columns requirements? — Simon, Oct 30 '17 at 17:36
Have you tried using a Pivot - see example [here](https://stackoverflow.com/a/15931734/1043031) or a Dynamic Pivot - see example [here](https://stackoverflow.com/a/27532568/1043031)? — chrisuae, Oct 30 '17 at 18:23

score 2 · Answer 1 · answered Oct 30 '17 at 17:28

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

Ilyes · Answer 2 · 2017-11-01T20:19:16.733

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:

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

Is there a way to include other columns(Here you have included just 1 ie Priority) in pivot ? Say If I have another column with different set of values. — ashish2199, Oct 31 '17 at 06:10

score 1 · Answer 3 · answered Oct 30 '17 at 17:32

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.

score 0 · Answer 4 · answered Oct 30 '17 at 17:27

0

You can use CASE statement or computed columns.

answered Oct 30 '17 at 17:27

bjnr

3,353
1
18
32

How to use computed columns for this ? – ashish2199 Oct 30 '17 at 17:50

Convert values of rows into columns

4 Answers4