Question 21132 in Mysql Question posted in
The official MySQL documentation can be found here.

Return multiple values based in different criteria into the same query SQL – Mysql

I’m pulling from a database which has multiple records for the same id, and I need to pull it all into the same table output:

ID Type Value Value2
1 Age 10
1 Height 136
1 Name Jeff
2 Age 12
2 Height 156
2 Name Ben

And I want my output to look like this:

ID Name Age Height
1 Jeff 10 136
2 Ben 12 156

I’ve tried self refrencing the table, but I can’t work out how to do multiple WHERE statements to return different values based on different inputs.

Thanks!

Tags:

2

Answers


  1. 0

    We can use conditional aggregation here to unpivot the data:

    SELECT
    ID,
    MAX(CASE WHEN Type = 'Name'   THEN Value2 END) AS Name,
    MAX(CASE WHEN Type = 'Age'    THEN Value END) AS Age,
    MAX(CASE WHEN Type = 'Height' THEN Value END) AS Height
FROM yourTable
GROUP BY ID
ORDER BY ID;
    Login or Signup to reply.
  2. 0

    You can use aggregation with case when in order to built something like this:

    CREATE TABLE mytable (
    ID INT,
    Type VARCHAR(255),
    Value INT,
    Value2 VARCHAR(255)
);

INSERT INTO mytable (ID, Type, Value, Value2)
VALUES (1, 'Age', 10, NULL),
       (1, 'Height', 136, NULL),
       (1, 'Name', NULL, 'Jeff'),
       (2, 'Age', 12, NULL),
       (2, 'Height', 156, NULL),
       (2, 'Name', NULL, 'Ben');

SELECT ID
      ,MAX(CASE TYPE WHEN 'Name' THEN Value2 END) AS 'Name'
      ,MAX(CASE TYPE WHEN 'Age' THEN Value END) AS 'Age'
      ,MAX(CASE TYPE WHEN 'Height' THEN Value END) AS 'Height'
FROM mytable
GROUP BY ID;

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search