SQL convert data from columns into one row

Question

I have a table in SQL that looks like:

name  age apples banana kiwi
mary  5   R3.00  null   null
mary  5   null   null   r6.00
mary  5   null   r9.50  null
joane 8   null   r9.60  null
joane 8   r5.00  null   null

I need to convert it to:

name    age apples  banana  kiwi
joane   8   r5.00   r9.60   (null)
mary    5   R3.00   r9.50   r6.00

How do I do this is SQL?

GROUP BY and for example MAX. What do you want to happen if Mary has two different Kiwi values? — jarlh, Nov 03 '15 at 10:58
Yes, but how do I get the fruit to lend up on the same row per person? — Miss R, Nov 03 '15 at 10:59
Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Nov 03 '15 at 11:04
Why do you have 3 rows for Mary, why don't you do an update not an insert and have Mary stored as one row initially? — Aravona, Nov 03 '15 at 11:25

score 0 · Answer 1 · edited Nov 03 '15 at 11:03

0

Try

select sum(age), sum(apples), sum(banana), sum(kiwi) from table group by name

edited Nov 03 '15 at 11:03

MusicLovingIndianGirl

5,909
9
34
65

answered Nov 03 '15 at 11:02

Altynbek

11
7

thank you. what if the values within the columns are not rand values, instead it is text...how do I do it? – Miss R Nov 03 '15 at 11:04
it is a bad idea to `sum` age ;) – Ilia Maskov Nov 03 '15 at 11:39

score 0 · Answer 2 · answered Nov 03 '15 at 11:10

0

You should just group by name and take min/max of each value

select 
    name, max(age) age, max(apples) apples, max(banana) banana, max(kiwi) kiwi
from table group by name

answered Nov 03 '15 at 11:10

Ilia Maskov

1,858
16
26

thank you. what if the values within the columns are not rand values, instead it is text...how do I do it? – Miss R Nov 03 '15 at 11:12
@MissR It would be ok, `MAX` aggregate function is defined for both text and number values. – Ilia Maskov Nov 03 '15 at 11:18

score 0 · Answer 3 · answered Nov 03 '15 at 11:23

Use DISTINCT & NOT NULL in a sub query as the values are text not numbers (so SUM wont work).

SELECT DISTINCT yt.name, yt.age, 
(SELECT yta.apples FROM yourtable yta WHERE yta.name = yt.name AND yta.apples IS NOT NULL) AS apples, 
(SELECT ytb.banana FROM yourtable ytb WHERE ytb.name = yt.name AND ytb.banana IS NOT NULL) AS banana, 
(SELECT ytk.kiwi FROM yourtable ytk WHERE ytk.name = yt.name AND ytk.kiwi IS NOT NULL) AS kiwi
FROM yourtable yt

OUTPUT

name    age apples  banana  kiwi
joane   8   r5.00   r9.60   (null)
mary    5   R3.00   r9.50   r6.00

SQ FIDDLE: http://sqlfiddle.com/#!3/88bc53/11/0

N.B. Also please consider your naming convention, either all plural or not (apples & kiwi/banana).

subqueries are redundant here i think, we could do this with just aggregation — Ilia Maskov, Nov 03 '15 at 11:42
@agent5566 Possibly, it depends on what other data is populated in the fruit fields. — Matt, Nov 03 '15 at 11:48

SQL convert data from columns into one row

3 Answers3