Pivot with 2+ columns (using CROSSTAB?)

Question

I have a table deflator that is defined as:

 Table "deflator"
 Column | Type | Modifiers
-------------+-------------------+-----------
country_code | smallint | not null
country_name | character varying | not null
year | smallint | not null
deflator | numeric |
source | character varying |

Sample output from this table looks like:

country_code | country_name | year | deflator | source
-------------+---------------+------+----------+----------
 1 | country_1 | 2016 | 12 | source_1
 1 | country_1 | 2015 | 11 | source_2
 1 | country_1 | 2014 | 10 | source_2
 2 | country_2 | 2016 | 15 | source_1
 2 | country_2 | 2015 | 14 | source_1
 2 | country_2 | 2014 | 13 | source_2
 3 | country_3 | 2016 | 18 | source_1
 3 | country_3 | 2015 | 17 | source_2
 3 | country_3 | 2014 | 16 | source_3
(9 rows)

I use the following query to pivot the table if I exclude the column source:

SELECT
 *
FROM CROSSTAB (
 'SELECT
 country_code
 , country_name
 , year
 , deflator
 FROM dimension.master_oecd_deflator
 ORDER BY 1;'
 , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
 "country_code" SMALLINT
 , "country_name" TEXT
 , "2014" NUMERIC
 , "2015" NUMERIC
 , "2016" NUMERIC
);

The above query gives me:

country_code | country_name | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
 1 | country_1 | 12 | 11 | 10 |
 2 | country_2 | 15 | 14 | 13 |
 3 | country_3 | 18 | 17 | 16 |

But because the source of the deflator varies from year to year for each country I want to include the source column in the pivot for my desired output to look like:

country_code | country_name | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
 1 | country_1 | 12 | source_1 | 11 | source_2 | 10 | source_2
 2 | country_2 | 15 | source_1 | 14 | source_1 | 13 | source_2
 3 | country_3 | 18 | source_1 | 17 | source_2 | 16 | source_3

How do I modify this query to give me the desired output? (With the source for each year listed next to the year). Is this even possible?

Answer 1

Yes it is possible, here is the solution:

WITH cte AS 
 ( SELECT * 
 FROM CROSSTAB 
 ( 'SELECT country_code, country_name, year, 
 deflator || '',''|| source
 FROM deflator 
 ORDER BY 1;', 
 $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
 ) AS "ct" ( "country_code" SMALLINT, 
 "country_name" TEXT , 
 "2014" text, "2015" text, "2016" text
 )
 )
SELECT country_code, country_name, 
 split_part("2014",',',1) AS "2014", 
 split_part("2014",',',2) AS "2014_source", 
 split_part("2015",',',1) AS "2015", 
 split_part("2015",',',2) AS "2015_source", 
 split_part("2016",',',1) AS "2016", 
 split_part("2016",',',2) AS "2016_source" 
FROM cte ;

Answer 2

Saddam has a smart solution, but it carries some weaknesses. Imagine a source named 'Fresno, CA' (with comma in the string). split_part() would be fooled by the separator character in the string ...

To avoid such corner case problems and preserve original data types, use a (well-defined!) row type instead. You can create a composite type permanently with CREATE TYPE or register a temporary one with CREATE TEMP TABLE:

CREATE TEMP TABLE defso (def numeric, so varchar); -- once per session!
SELECT country_code
 , country_name
 , (d14).def AS deflator_2014 -- note the parentheses!
 , (d14).so AS source_2014
 , (d15).def AS deflator_2015
 , (d15).so AS source_2015
 , (d16).def AS deflator_2016
 , (d16).so AS source_2016
FROM crosstab (
 'SELECT country_code, country_name, year, (deflator, source)::defso
 FROM deflator
 ORDER BY 1'
 , 'SELECT generate_series(2014, 2016)::int2'
 ) AS ct (country_code int2
 , country_name text
 , d14 defso
 , d15 defso
 , d16 defso
 );

I also removed the unnecessary CTE and simplified a bit.

---

While dealing with only a hand full of years, you can do without crosstab() and use self-joins:

SELECT country_code, country_name
 , d14.deflator AS deflator_2014
 , d14.source AS source_2014
 , d15.deflator AS deflator_2015
 , d15.source AS source_2015
 , d16.deflator AS deflator_2016
 , d16.source AS source_2016
FROM (SELECT * FROM deflator WHERE year = int2 '2014') d14
FULL JOIN (SELECT * FROM deflator WHERE year = int2 '2015') d15 USING (country_code, country_name)
FULL JOIN (SELECT * FROM deflator WHERE year = int2 '2016') d16 USING (country_code, country_name)
ORDER BY country_code;

Using FULL [OUTER] JOIN since we can't assume a row for every combination of (country_code, year). This way we get the same result as with the crosstab query above.

Including country_name in the join condition seems redundant, but if we don't, we have to use COALESCE(d14.country_name, d15.country_name, d16.country_name) AS country_name to defend against missing rows. This functionally dependent value shouldn't be in the table to begin with. Should be in a country table in a properly normalized schema.