CODEWITHSUNDEEP
sqlhive
Tanvir
Tanvir

Reputation: 174

SQL: GROUP BY with one column

I have a table like this:

name    id1     id2     id3     id4
Mark    aaa     null    null    null
Mark    null    bbb     null    null
Mark    null    null    null    null
John    null    null    eee     null
John    ccc     null    null    null
John    null    ddd     null    null
John    null    null    null    null

So, I have 5 columns as, name, id1, id2, id3 and id4 -all are string values. I need to go for a GROUP BY for name only; which will have all the values except null values. So the final output will be something like this:

    name    id1     id2     id3     id4
    Mark    aaa     bbb     null    null
    John    ccc     ddd     eee     null

Can anyone please help me to do that?

Upvotes: 1

Views: 96

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31889

Use MAX:

SELECT
    name,
    MAX(id1) AS id1,
    MAX(id2) AS id2,
    MAX(id3) AS id3,
    MAX(id4) AS id4
FROM tbl
GROUP BY name;

Upvotes: 3

AntDC
AntDC

Reputation: 1917

Max on string... Z is greater than A. Any string is greater than null. Don't forget the GROUP BY

create table #test(
name varchar(10),
id1 varchar(10),
id2 varchar(10),
id3 varchar(10),
id4 varchar(10)
)

INSERT INTO #test SELECT 'Mark', 'aaa', null, null, null;
INSERT INTO #test SELECT 'Mark', null, 'bbb', null, null;
INSERT INTO #test SELECT 'Mark', null, null, null, null
INSERT INTO #test SELECT 'John', null, null, 'eee', null
INSERT INTO #test SELECT 'John', 'ccc', null, null, null
INSERT INTO #test SELECT 'John', null, 'ddd', null, null
INSERT INTO #test SELECT 'John', null, null, null, null

SELECT
    name,
    MAX(id1) AS id1,
    MAX(id2) AS id2,
    MAX(id3) AS id3,
    MAX(id4) AS id4
FROM #test
Group By name

drop table #test

Upvotes: 1

Edwin van der V
Edwin van der V

Reputation: 238

Use something like this:

select 
  name,
  max(id1) id1,
  max(id2) id2,
 max(id3) id3,
  max(id4) id4
from
(
  select name, id1, id2, id3, id4,
    row_number() over(partition by name
                      order by name) seq
  from yourtable
) d
group by name

Upvotes: 1

Related Questions