CODEWITHSUNDEEP
sqlsql-server
user1725619
user1725619

Reputation:

Remove duplicate rows when using CASE WHEN statement

I am currently generating a table which converts the rows value to the new column, the following is my code:

SELECT ref_no,
    (CASE WHEN code = 1 THEN code END) AS 'count_1',
    (CASE WHEN code = 2 THEN code END) AS 'count_2',
    (CASE WHEN code = 3 THEN code END) AS 'count_3',
    (CASE WHEN code = 4 THEN code END) AS 'count_4',
    (CASE WHEN code = 5 THEN code END) AS 'count_5',
    (CASE WHEN code = 6 THEN code END) AS 'count_6'
FROM data"

The output is:

enter image description here

However, I needs those duplicated rows to be combined, is there any way to do? I don't need to sum up those values as there is no overlap among them.

I've tried with group by but it does not work as expected:

enter image description here

My expected out put is like:

ref   c_1  c_2  c_3  c_4  c_5  c_6
1     1     2    3    -    -    -

This shows adding ORDER BY clause does not work in my context. enter image description here

Updated: complete query in sqldf

enter image description here

Upvotes: 0

Views: 10082

Answers (3)

sagi
sagi

Reputation: 40481

The answer is: YES

By using GROUP BY and MAX like this:

SELECT ref_no,
    max(CASE WHEN code = 1 THEN code END) AS 'count_1',
    max(CASE WHEN code = 2 THEN code END) AS 'count_2',
    max(CASE WHEN code = 3 THEN code END) AS 'count_3',
    max(CASE WHEN code = 4 THEN code END) AS 'count_4',
    max(CASE WHEN code = 5 THEN code END) AS 'count_5',
    max(CASE WHEN code = 6 THEN code END) AS 'count_6'
FROM data
GROUP BY ref_no
ORDER BY ref_no

Upvotes: 1

Pete
Pete

Reputation: 59

The easiest would either be to use GROUP BY or a PIVOT function.

GROUP BY example below:

SELECT ref_no,
    sum(CASE WHEN code = 1 THEN code ELSE 0 END) AS 'count_1',
    sum(CASE WHEN code = 2 THEN code ELSE 0 END) AS 'count_2',
    sum(CASE WHEN code = 3 THEN code ELSE 0 END) AS 'count_3',
    sum(CASE WHEN code = 4 THEN code ELSE 0 END) AS 'count_4',
    sum(CASE WHEN code = 5 THEN code ELSE 0 END) AS 'count_5',
    sum(CASE WHEN code = 6 THEN code ELSE 0 END) AS 'count_6'
FROM data
GROUP BY ref_no

A really long way of doing this using your existing code and a CTE table:

WITH results as (
SELECT ref_no,
    (CASE WHEN code = 1 THEN code END) AS 'count_1',
    (CASE WHEN code = 2 THEN code END) AS 'count_2',
    (CASE WHEN code = 3 THEN code END) AS 'count_3',
    (CASE WHEN code = 4 THEN code END) AS 'count_4',
    (CASE WHEN code = 5 THEN code END) AS 'count_5',
    (CASE WHEN code = 6 THEN code END) AS 'count_6'
FROM data) 

SELECT 
    ref_no
  , sum(coalesce(count_1),0)  -- for sum
  , max(coalesce(count_1),0)  -- for just the highest value
  -- Repeat for other ones 
FROM
  results
GROUP BY
  ref_no

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220952

You could use PIVOT for this

SELECT *
FROM (
  SELECT ref_no, code FROM data
) data
PIVOT (
  max(code) FOR code IN ([1], [2], [3], [4], [5], [6])
) pivoted

Upvotes: 0

Related Questions