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-%|'

The accepted answer on the question I linked does not use ISNULL. Note the use of COALESCE in DrM's answer below. — Robert Harvey, Jan 14 '13 at 18:33

score 4 · Accepted Answer · answered Jan 14 '13 at 18:25

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-%|'

As someone used to || meaning logical or, [this answer](https://stackoverflow.com/questions/23372550/what-does-sql-select-symbol-mean) may also be useful — havlock, Jul 11 '17 at 15:24

SQLite combine two columns

1 Answers1