I have table with few columns but I am interested in two of them:

1. name
2. type - it can be one of tree values: 1, 2, 3

All I want to create is a table where in first column I will SELECT value from column name where type = 1, in second column value from column name where type = 2, in last column value from column name where type = 3

I tried to create a subquery (I want to do it via CTE) but I got an error about the subquery returns more than 1 value. I tried something with case clause but its not working anyway. I was thinking about UNION it but I am not sure.
This is how the basic table looks like:

SELECT name, type
FROM table1

Table:

And how I want to see SELECT it:

I just don't know how to show values with where type = 1 as one column, type = 2 as second column and type =3 as third.

CodePudding user response：

For MySQL;

SELECT
  IF(type=1, name, NULL) as "Product with type 1",
  IF(type=2, name, NULL) as "Product with type 2",
  IF(type=3, name, NULL) as "Product with type 3"
FROM table1

For T-SQL;

SELECT
  CASE WHEN type=1 THEN name ELSE NULL END as "Product with type 1",
  CASE WHEN type=2 THEN name ELSE NULL END as "Product with type 2",
  CASE WHEN type=3 THEN name ELSE NULL END as "Product with type 3"
FROM table1

CodePudding user response：

I think the most simple way is using case to separate the columns according to the type, but then you will have to handle the nulls that will remain in the cells

SELECT CASE WHEN type = 1 THEN  name ELSE NULL END
    , CASE WHEN type = 2 THEN name ELSE NULL END
    , CASE WHEN type = 3 THEN name ELSE NULL END
FROM table1
enter code here