CODEWITHSUNDEEP
postgresqlpivotcrosstab
f.ashouri
f.ashouri

Reputation: 5687

Repeat a column, duplicating it into more identical columns in the same row

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

Answers (2)

wildplasser
wildplasser

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

Craig Ringer
Craig Ringer

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

Related Questions