Jake Jake - 1 year ago 68
SQL Question

SQL: duplicating rows with an additional running sequence field

I am trying to create a select statement to duplicate each unique row (by 'location' field) by 361 times (0 to 360). An additional column is also created showing the sequence (0 to 360). The purpose of this is to calculate the coordinates around each of the lat & long using the radius and degree fields to render it as a circle within Tableau.

This is the original sample table.

enter image description here

This is the final desired output.

enter image description here

Can anyone help?

sample data in comma delimited~


EDIT: There are 50 plus rows in the original table.

Answer Source

I don't believe Vertica has the equivalent of Postgres's generate_series(). You can mock it, if you have a table with at least 361 rows:

with n as (
      select row_number() over () - 1 as n
      from (select s.* from sample s limit 361) s
select s.*, n.n as degress
from sample s cross join

In Vertica, order by is optional for the row_number() function.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download