Search code examples
sqlmergerecordset

Merge recordset values in SQL query

Given the following result set:

enter image description here

I want to merge:

  1. 'Verifying' and 'Verified' rows as Verification_stage,
  2. 'Reviewing' and 'Reviewed' rows as Reviewing_stage and
  3. 'Analyzing' and 'Analyzed' rows as Analysis_stage
  4. Have the respective integers summarized

so I get the following resultset in stead:

HAUT-53 | Verification_stage | 677.579 | 6
HAUT-53 | Reviewing_stage    | 516.409 | 2
HAUT-53 | Open               | 70.629  | 1
HAUT-53 | Implementing       | 7       | 1
HAUT-53 | Analysis_stage     | 12.027  | 2

Any ideas?

Solution

  • Use a case expression to merge, in a derived table. Then GROUP BY its result:

    select c1, c2, sum(c3), sum(c4)
from
(
    select c1, 
           case when c2 in ('Verifying', 'Verified') then 'Verification_Stage'
                when c2 in ('Reviewing', 'Reviewed') then 'Reviewing_Stage'
                when c2 in ('Analyzing', 'Analyzed') then 'Analyzing_Stage'
                else c2 
           end c2,
           c3, c4
    from tablename
) dt
group by c1, c2

    Using a derived table (the sub-query), means that you don't have to repeat the case expression. Less error prone, easier to maintain - and also ANSI SQL compliant!