sql reshaping: converting a narrow triplets store to a wide table and combining related attributes and concatenating multiple entries within cells

_sql_reshaping_triplets_to_to_columns.md

SQL reshaping:

• converting a narrow triplets store to a wide table and combining related attributes and concatenating multiple entries within cells

What happening in the first solution:

• we create a row per country via group by.
• then we create each column by an aggragate on a case.
• the case could be eliminated using a sub query
• also we concat result using a mysql extension GROUP_CONCAT() or PostgreSQL, SQL Server 2017 and Azure SQL ext STRING_AGG()

Refrences:

• http://sqlfiddle.com/#!9/31758/1
• https://stackoverflow.com/questions/2255640/mysql-reshape-data-from-long-tall-to-wide
• https://stackoverflow.com/questions/2444708/sqlite-long-to-wide-formats
• https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
• https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings

triplets.sql

-- schema
CREATE TABLE triplets (
    id INT NOT NULL AUTO_INCREMENT,
    country   VARCHAR(35),
    attr  VARCHAR(35),
    val VARCHAR(35),
    PRIMARY KEY(Id)
);

INSERT INTO triplets
    (country,attr, val)
VALUES
("US","President","Obama" ),
("US","Currency","Dollar"),
("US","Capitol City","Washington DC"),
("China","President","Hu"),
("China","Currency","Yuan"),
("China","Capitol City","Bejing"),
("Israel","Currency","Shekel"),
("Israel","President","Peres"),
("Israel","Capitol City","Jerusalem"),
("Israel","Language","Hebrew"),
("Israel","Language","Arabic"),
("Israel","Capitol City","Tel Aviv"),
("China","Language","Cantoneese"),
("China","Language","Mandarin"),
("US","Language","English"),
("Japan","Language","Japaneese"),
("Japan","Currency","Yen"),
("Japan","Capitol City", "Tokyo"),
("UK","Language", "English"),
("UK","Currency", "Pound"),
("UK","Capitol City", "London"),
("UK","Monarch", "Elizabeth II"),
("UK","Prime Minister", "Theresa May"),
("Japan","Emperor", "Akihito"),
("Japan","Prime Minister", "Shinzō Abe"),
("Israel","Prime Minister", "Binyamin Netanyahu")
;


  SELECT Country,
         GROUP_CONCAT(CASE WHEN attr IN ("President","Prime Minister","Monarch","Emperor")  THEN val ELSE NULL END) as Leader,
         MAX(CASE WHEN attr = "Currency" THEN val ELSE NULL END) as Currency,
         GROUP_CONCAT(CASE WHEN attr = "Capitol City" THEN val ELSE NULL END) as Capitol,
         GROUP_CONCAT(CASE WHEN attr = "Language" THEN val ELSE NULL END) as Lang
    FROM triplets
GROUP BY country
  ORDER BY country;
;

SELECT * 
FROM triplets
ORDER BY country;