SQLite combine two columns

Question

This query returns two separate columns containing operating system information, since the data is automatically written into the DB table based on a scanning tool I am unable to manually alter the columns. I am attempting to write a query that combines the two rows into one (filling in the blanks from the operating_system row with the data in the operating_system_version row.

The query I have now is as follows:

SELECT device_type, operating_system, operating_system_version
FROM DEVICES
WHERE user_name like '|%front-%|'
OR user_name like '|%back-%|'
OR user_name like '|%ap-%|'
OR user_name like '|%me-%|'
OR user_name like '|%mg-%|'

and it returns:

device_type    operating_system        operating_system_version


desktop         windows xp
desktop                                 windows 7 professional
desktop         windows xp              
desktop                                 windows 7 professional

I would like it to return something like this:

device_type    OS


desktop         windows xp
desktop         windows 7 professional                        
desktop         windows xp              
desktop         windows 7 professional  

I've tried adding in || like below but it returned no data:

SELECT device_type, operating_system||operating_system_version
FROM DEVICES
WHERE user_name like '|%front-%|'
OR user_name like '|%back-%|'
OR user_name like '|%ap-%|'
OR user_name like '|%me-%|'
OR user_name like '|%mg-%|'                       

Answer 1

SELECT device_type, coalesce(operating_system,operating_system_version) AS OS
FROM DEVICES
WHERE user_name like '|%front-%|'
OR user_name like '|%back-%|'
OR user_name like '|%ap-%|'
OR user_name like '|%me-%|'
OR user_name like '|%mg-%|'

This will select operating system, or if null, select operating_system_version and return it as the column name OS.

If you want to concatonate the two fields, try

SELECT device_type, coalesce(operating_system,'') || coalese(operating_system_version,'') AS OS
FROM DEVICES
WHERE user_name like '|%front-%|'
OR user_name like '|%back-%|'
OR user_name like '|%ap-%|'
OR user_name like '|%me-%|'
OR user_name like '|%mg-%|'