Combine 3 rows into a single row

Question

I got a output like by following

ID Name  Col1  Col2  Col3  
1  ABC    X     0     0
2  ABC    0     Y     0 
3  ABC    0     0     Z

I need this followed by

ID Name  Col1  Col2  Col3  
1  ABC    X     Y     Z

I tried group by Name but it is not working

Could you give us a little more information on what you've already tried? Putting your data in a more accessible format will further increase your chances of getting a helpful answer (reduce it to the minimum set needed, inline it in your question). — markusthoemmes, Aug 24 '18 at 09:34
On the question itself: How do you envision the rows to be combined? A sample result of the query you're looking for will be helpful as well. — markusthoemmes, Aug 24 '18 at 09:36
What rule do you want applied? Ignore digits and use letters only? Is that the rule? Or what else? And if that is the rule, what if there are two rows for the same name with contradictory letters? Which to choose? — Thorsten Kettner, Aug 24 '18 at 09:49

Fahmi · Answer 1 · 2018-08-24T10:05:20.627

0

Try group by with aggrgation

select name, max(col1),max(col2),max(col3)
from tablename
group by name

edited Aug 24 '18 at 10:05

answered Aug 24 '18 at 09:35

Fahmi

I tried this but my columns are nvarchar, Its throwing error like by followed "Operand data type nvarchar is invalid for sum operator." – Rajasekar Baskar Aug 24 '18 at 09:44
modified the query, can you please check with max operator – Fahmi Aug 24 '18 at 09:50
Again error "Conversion failed when converting the nvarchar value 'X' to data type int." – Rajasekar Baskar Aug 24 '18 at 09:57
check here http://sqlfiddle.com/#!9/47ba6c/2 – Fahmi Aug 24 '18 at 10:05
@RajasekarBaskar Are you dure that you use mysql at all? The error message you quote in your comment comes from ms sql server. – Shadow Aug 24 '18 at 12:32

score 0 · Answer 2 · answered Aug 24 '18 at 10:42

If the 0s where NULL, then this should work:

select name, max(col1), max(col2), max(col3)
from tablename
group by name;

The 0s are rather confusing. This may be closer to what you want:

select name, max(nullif(col1, '0')),
       max(nullif(col2,'0')), max(nullif(col3, '0'))
from tablename
group by name;

2 Answers2