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

Question

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!

Answer 1

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;

Answer 2

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;