CODEWITHSUNDEEP
t-sql
Christov
Christov

Reputation: 178

Merge rows in SQL query result

I have the following situation. A query that returns results in this format: 

N1   || 10 || 5    
N2   || 20 || 9    
N3   || 5  || 4

By some error that can't be fixed, N3 has a different name than N1, while they should be the same. I want to combine rows 1 and 3 in such a way that together they become: 

N1 || 15 || 9

So I want to merge row 1 and row 3. Using regular matrix notation the desired output is equal to: 

M(1,1) || M(1,2)+M(3,2) || M(1,3)+M(3,3).

What makes it difficult is that I can not define a temp table to store the results in as the database does not allow this. Is there a way to get to the desired output?

Upvotes: 0

Views: 109

Answers (1)

spencer7593
spencer7593

Reputation: 108370

You could use the existing query as an inline view, and GROUP BY to collapse the rows. Just use an expression that replaces 'N3' with 'N1'.

 SELECT IF(q.name1='N3','N1',q.name1) AS name1
      , SUM(q.val2)                   AS val2
      , SUM(q.val3)                   AS val3
   FROM (
          -- put here the query query that returns resultset
          -- 
          --  name1  val2  val3
          --  -----  ----  ----
          --  N1       10     5
          --  N2       20     9
          --  N3        5     4
          -- 
          SELECT ... AS name1
               , ... AS val2
               , ... AS val3
            FROM ...
        ) q
  GROUP BY IF(q.name1='N3','N1',q.name1)
  ORDER BY 1

NOTE: Replace name1, val2, val3 to match the column names returned by the query in the inline view.

Upvotes: 1

Related Questions