Take Distinct Key and join Flags from non-distinct

[#1]

Basically I want to take a table like this:

key	flag1	flag2	flag3
1	1	0	0
1	0	0	1
1	0	1	0
2	0	0	0
2	0	1	0
2	0	0	1

and make another table like this, that is only the distinct keys with flags joined to it:

key	flag1	flag2	flag3
1	1	1	1
2	0	1	1

I think there's an easy way to do this but I'm kind of slow today. Any help would be appreciated.

Thanks!

[jpapador · Accepted solution]

If a row can never have a 1 in multiple flags you could Sum the flag fields and group by Key

TableName:

LOAD

      Key,

      Sum(Flag1) as Flag1,

      Sum(Flag2) as Flag2,

      Sum(Flag3) as Flag3

Resident data

Group By Key;

[Anonymous]

Hi,

Table1:

LOAD

      Key,

      Count(Flag1) as Flag1,

      Count(Flag2) as Flag2,

      Count(Flag3) as Flag3

Resident Table

Group By Key;

[#4]

Thanks!