themorethelarrier themorethelarrier -3 years ago 80
SQL Question

SQL: storing coded rows as separate columns for a given ID

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.

Answer Source
    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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download