Summarize mulitpe records into one

[rdy]

I have multiple records per individual, based on an individual type. Each one will either be A or I. (Active or inactive.)

I need to boil that down to one record per individual, that follows this business rule:

"If an individual has any active records, they are active."

i.e.

Three records:
John Doe, Type 1, A
John Doe, Type 2, A
John Doe, Type 3, I

One summary record:
John Doe, A

How can I do this with DataStage and SQL? (If I were writing a stored procedure I would step through the three records and keep an "active indicator".)

[kcbland]

Code: Select all

   select name from yourtable
   group by name 
   having sum(case when status = 'A' then 1 else 0 end) > 0

[vmcburney]

How about:
SELECT NAME, MIN(STATUS)
FROM YOURTABLE
GROUP BY NAME

Assuming here that A and I are the only valid values for STATUS and you want to return both Active and Inactive names. A will be returned ahead of I using the MIN function.