Join data from rows to 1 row depending on the number of rows

debugcn Published at Dev

Grego

I have the following tables:

user
userId   name
1        Sam
2        Harold
3        John


othertable
id      id2      number
1       111      12
1       222      23
1       333      33
2       111      12
2       444      11
3       555      12
3       222      44

The user table's userId matches the othertable's id column. Ideally I'd like to join the content of othertable to user depending on how many rows are present for that id. That's the output I'm aiming for:

e.g.
user
    userId   name    111    222   333   444   555
    1        Sam     12     12    33
    2        Harold  12                 11
    3        John           44                12

Any idea?

Update: The id2's values are limited. Only valid values 111, 222, 333, 444 and 555.

ZHui

you may try out this 1... not sure if it meet you requirement...

CREATE TABLE users 
(
    userId int,
    name varchar(max)
) 

INSERT INTO USERS VALUES
(1, 'Sam'),
(2, 'Harold'),
(3, 'John')

CREATE TABLE othertable 
(
    id int,
    id2 int,
    number int
)

INSERT INTO othertable VALUES
(1, 111, 12),
(1, 222, 23),
(1, 333, 33),
(2, 111, 12),
(2, 444, 11),
(3, 555, 12),
(3, 222, 44)

SELECT
    u.userId,
    u.name,
    SUM(CASE WHEN (id2=111) THEN number ELSE 0 END) AS [111],
    SUM(CASE WHEN (id2=222) THEN number ELSE 0 END) AS [222],
    SUM(CASE WHEN (id2=333) THEN number ELSE 0 END) AS [333],
    SUM(CASE WHEN (id2=444) THEN number ELSE 0 END) AS [444],
    SUM(CASE WHEN (id2=555) THEN number ELSE 0 END) AS [555]
FROM othertable o 
INNER JOIN users u ON o.id = u.userId
GROUP BY u.userId, u.name

Please have a try. :)

UPDATE: Sorry, I'm not really familiar with MySQL. But I did tried my best by changing the query into subquery and hope this can help you. If this doesn't meet you requirement, I hope some other people can help you.

UPDATE 2: Avoid using PIVOT

Collected from the Internet

Please contact [email protected] to delete if infringement.