CODEWITHSUNDEEP
mysqlsql
Kliver Max
Kliver Max

Reputation: 5299

How to separate one column to several?

I have a table:

DataTable:
 id int,
 user_id int,
 field_id int,
 value varchar

DataExample:

id    user_id    field_id   value 
 1       1            1       'Peter'
 2       1            2       56
 3       1            3       100
 4       2            1       'Buzz'
 5       2            2       47
 6       2            3       120

I want to separate value column by field_id property:

Name    Age     Score
Peter   56      100
Buzz    47      120

I tried:

SELECT
    CASE
        WHEN field_id = 1 THEN  value
    END as Name,   
    CASE
        WHEN field_id = 2 THEN  value
    END as Age,
    CASE
        WHEN field_id = 3 THEN  value
    END as Score
    FROM Users u
    INNER JOIN DataTable t ON (t.user_id=u.id)

But got:

Name    Age     Score
Peter   null     null
null    56       null
null    null     100
Buzz    null      null
null    47       null
null    null      120

Any advises?

Upvotes: 0

Views: 55

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176214

You can apply GROUP BY to your query. Name is probably not unique you can consider grouping by id:

SELECT id, MAX(Name) AS Name, MAX(Age) AS Age, MAX(Score) AS Score
FROM (
  SELECT u.id
  CASE
    WHEN field_id = 1 THEN  value
  END as Name,   
  CASE
    WHEN field_id = 2 THEN  value
  END as Age,
  CASE
    WHEN field_id = 3 THEN  value
  END as Score
  FROM Users u
  INNER JOIN DataTable t ON (t.user_id=u.id)
) AS sub
GROUP BY id;

Upvotes: 2

Related Questions