CODEWITHSUNDEEP
phpmysqlsql
Adam Bieńkowski
Adam Bieńkowski

Reputation: 585

Is it possible to group rows this way using MySQL?

I have a MySQL table like:

ID, USER, PARAM, VAL
--------------------
1 | 1 | NAME | ADAM
2 | 1 | AGE  | 15
3 | 2 | NAME | EVA
4 | 2 | AGE  | 16
5 | 3 | NAME | CRISS
6 | 3 | AGE  | 14

And I'm curious if there is a query which would give me something similar to:

1 | ADAM | 15
2 | EVE  | 16
3 | CRISS| 14

So far I'm just using the below query and grouping the records in a loop.

SELECT * FROM table WHERE PARAM ='NAME' OR PARAM = 'AGE'

I tried to use GROUP but without success.

Upvotes: 20

Views: 728

Answers (5)

n-dru
n-dru

Reputation: 9430

Use join on the same table:

SELECT a.USER user_id, a.VAL user_name, b.VAL user_age
FROM `table` a
INNER JOIN `table` b ON a.USER = b.USER
WHERE a.PARAM = 'NAME'
AND b.PARAM = 'AGE'

Result:

user_id     user_name   user_age
1           ADAM        15
2           EVA         16
3           CRISS       14

Upvotes: 15

bdifferent
bdifferent

Reputation: 703

You could do a query like that:

SELECT t1.user,
        (
           SELECT val FROM tab1 t2 
           WHERE t2.user = t1.user 
             AND t2.param = 'Name'
        ) name,
        (
           SELECT val FROM tab1 t2 
           WHERE t2.user = t1.user 
             AND t2.param = 'Age'
        ) age
    FROM tab1 t1
    GROUP BY user

Another possibility is group_concat, but then you have all values listen in one columns.

SELECT user, group_concat(param, ':', val)
    FROM tab1
    GROUP BY user

Upvotes: 5

Chintan
Chintan

Reputation: 1204

There's no need to use JOIN. Try this:

SELECT USER,
       MAX(CASE PARAM WHEN 'NAME' THEN VAL ELSE NULL END) AS NAME,
       MAX(CASE PARAM WHEN 'AGE' THEN VAL ELSE 0 END) AS AGE
FROM test 
GROUP BY USER;

Upvotes: 9

rdn87
rdn87

Reputation: 724

Hi i replicate your problem in my db example, try this query:

    SELECT t1.USER,VAL,(SELECT VAL AS AGE FROM table1 where USER = t1.USER AND PARAM = 'AGE') as AGE 
    FROM table1 t1
    WHERE param = 'NAME'

or use join on your table:

SELECT a.USER, a.VAL, b.VAL
FROM table1  t1
INNER JOIN table1 t2 
ON t1.USER = t2.USER
WHERE t1.PARAM = 'NAME'
AND t2.PARAM = 'AGE'

replace table1 with your table name.

Upvotes: 3

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can generate the pivot view by using the most commonly used technique as 

select
user,
max(case when param = 'NAME' then val end) as name,
max(case when param = 'AGE' then val end) as age
from mytable
group by user

Upvotes: 8

Related Questions