Reputation: 5687
I have table with 3 columns like this: (ID, city,city_pop).
I just want copy city_pop column 20 times. So the final table will look like something this: (ID, city, city_pop, city_pop1, city_pop2, ...city_pop20)
with same values as city_pop has. Is it possible?
Upvotes: 1
Views: 107
Reputation: 44250
-- CREATE TABLE cities20 AS
SELECT id,city
, city_pop AS city_pop0
, city_pop AS city_pop1
, city_pop AS city_pop2
, city_pop AS city_pop3
, city_pop AS city_pop4
, city_pop AS city_pop5
, city_pop AS city_pop6
, city_pop AS city_pop7
, city_pop AS city_pop8
, city_pop AS city_pop9
, city_pop AS city_pop10
, city_pop AS city_pop11
, city_pop AS city_pop12
, city_pop AS city_pop13
, city_pop AS city_pop14
, city_pop AS city_pop15
, city_pop AS city_pop16
, city_pop AS city_pop17
, city_pop AS city_pop18
, city_pop AS city_pop19
FROM cities
;
Upvotes: 4
Reputation: 325241
This is another job for crosstab
from the tablefunc
extension, though again it seems like a truly bizarre thing to want to do.
Setup of demo data and to load the tablefunc
extension:
CREATE TABLE cities (ID integer, city text, city_pop integer);
INSERT INTO cities VALUES (1,'Boganville',200), (2, 'Amity', 543);
CREATE EXTENSION tablefunc;
Query:
SELECT city, ct.* FROM crosstab(
'SELECT id, ''city_pop''||CASE WHEN x = 1 THEN '''' ELSE x::text END AS colname, city_pop FROM generate_series(1,20) x CROSS JOIN cities ORDER BY 1, x;'
) ct(
cityid integer,
city_pop integer, city_pop2 integer, city_pop3 integer, city_pop4 integer,
city_pop5 integer, city_pop6 integer, city_pop7 integer, city_pop8 integer,
city_pop9 integer, city_pop10 integer, city_pop11 integer, city_pop12 integer,
city_pop13 integer, city_pop14 integer, city_pop15 integer, city_pop16 integer,
city_pop17 integer, city_pop18 integer, city_pop19 integer, city_pop20 integer
) INNER JOIN cities ON (ct.cityid = cities.id);
Result:
city | cityid | city_pop | city_pop2 | city_pop3 | city_pop4 | city_pop5 | city_pop6 | city_pop7 | city_pop8 | city_pop9 | city_pop10 | city_pop11 | city_pop12 | city_pop13 | city_pop14 | city_pop15 | city_pop16 | city_pop17 | city_pop18 | city_pop19 | city_pop20
------------+--------+----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
Boganville | 1 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200
Amity | 2 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543 | 543
(2 rows)
Col list generated with:
SELECT string_agg( 'city_pop'||CASE WHEN x = 1 THEN '' ELSE x::text END || ' integer', ', ') FROM generate_series(1,20) x;
Upvotes: 2