I have a table that contains geocoded data for a set of addresses. The issue is that latitude and longitude are stored in the same column, but are differentiated by a code (0 or 1) in another column in the same table. Each address is marked with an unique id that appears twice in the column (1 for lat, 1 for long).
I would like to show lat and long on the same row as separate columns along with the corresponding ID.
select unique_id, max(case when code = 0 then geocoded_column end) as lat, max(case when code = 1 then geocoded_column end) as long from geocoded_table group by unique_id