CODEWITHSUNDEEP
mysqlsqloracle-database
James
James

Reputation: 93

MySQL think I need to use ROLLUP but i'm not sure

If i has a group of people identifiers (12,34,54,65) and a database with Keys (Gender, Age, Salary for example), such as:

Details table

{Person ID,   Key,    Value}
{12,           Gender,  Male}
{34,           Age,     40}
{54,           Salary,  30000}

Personnel table

ID
12
34
54
65

I want to create rows consisting of the powerset of {12,34,54,65} x {Age,Salary,Gender}, regardless of whether there is a data value in the Details table

Upvotes: 2

Views: 144

Answers (2)

corlettk
corlettk

Reputation: 13574

I guess what you're looking for is plain old:

select * from personnel p, details d where d.person_id = p.person_id

But that details table smells like a fundamentally broken design to me. What type is the value column? A string? and it's storing strings, integers, and currency values? Good luck with that. I hope you don't want to actually USE any of this data, let alone search on it.

EDIT: Nope... I now think Devart is spot-on. I didn't understand the question. Sigh.

Upvotes: 0

Devart
Devart

Reputation: 121922

You do not need rollup, you need to pivot table -

SELECT p.id, d.gender, d.age, d.salary FROM personnel p
  JOIN (
    SELECT person_id,
      MAX(IF(`key` = 'gender', `value`, NULL)) AS gender,
      MAX(IF(`key` = 'age', `value`, NULL)) AS age,
      MAX(IF(`key` = 'Salary', `value`, NULL)) AS salary
    FROM
      details
    GROUP BY
      person_id) d
    ON p.id = d.person_id;

Upvotes: 3

Related Questions