CODEWITHSUNDEEP
sqlpostgresql
Lars
Lars

Reputation: 173

Postgresql: Split line into 4 lines

My concern is to split a single line into 4 lines using a SQL script. It is that I get in an SQL Result the year, the quarter, the month and a x worthy value. Now I would also like to spend the week of the month (1-4) without having to add this as a column of the table. Likewise, the value should be divided by four. Thus, from this result:

year | quarter | month | value
2016 | 1       | 1     | 78954

This result:

year | quarter | month | week | value
2016 | 1       | 1     | 1    | 19738,5
2016 | 1       | 1     | 2    | 19738,5
2016 | 1       | 1     | 3    | 19738,5
2016 | 1       | 1     | 4    | 19738,5

I have no idea how I could implement this. I hope anyone can help me.

Best regards

Upvotes: 0

Views: 134

Answers (4)

JosMac
JosMac

Reputation: 2302

Or you can do it in very scientifically looking way :-)

WITH series as (select generate_series(1,4,1) as week ),
  data as (SELECT 2016 as year, 1 as quarter, 1 as month, 78954 as value)
  SELECT d.year, d.quarter, d.month, s.week, d.value/(SELECT count(*) FROM series)::numeric 
  FROM data d JOIN series s ON true

Upvotes: 0

user330315
user330315

Reputation:

You can also use `generate_series() for that:

select t.year, t.quarter, t.month, w.week, t.value / 4
from the_table t
 cross join generate_series(1,4) as w(week)
order by t.year, t.quarter, w.week;

Using generate_series() is more flexible if you need to change the number of repeated rows you want - although "weeks per quarter" doesn't really need that flexibility.

Upvotes: 0

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

You could do it with a cartesian join:

SELECT a.year, a.quarter, a.month, b.week, a.value
  FROM a, (SELECT UNNEST(ARRAY[1, 2, 3, 4]) as week) b

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Just use union:

select year, quarter, month, 1 as week, value / 4 as value
union all
select year, quarter, month, 2 as week, value / 4 as value
union all
select year, quarter, month, 3 as week, value / 4 as value
union all
select year, quarter, month, 4 as week, value / 4 as value

Upvotes: 0

Related Questions