I have a table that I want to pivot/reshape from long to wide.
The long format has 3 columns: zipcode, category, zipnum '12345', 'A', '1' '40348', 'A', '2' '16132', 'B', '1' '09428', 'B', '2' '14818', 'B', '3' '93182', 'C', '1'
The column zipnum is the row number for each zip code, grouped by category. So zipnum is unique for each zipcode within a category. The wide format that I am trying to achieve has the columns category, zipcode1, …, zipcodeN, where N is the maximum value of zipnum in the entire table. In this example N=3, but in my actual dataset N is about 2000.
category, zipcode1, zipcode2, zipcode3 'A', '12345', '40348', NULL 'B', '16132', '09428', '14818' 'C', '93182', NULL, NULL
I’m relatively new to SQL – I know how to do this very easily in Python, R, etc. but can’t find a way to do it with MySQL. To be frank I don’t really even know where to start.
I would suggest that you combine them into a single comma-separated string:
select category, group_concat(zip_code order by zipnum) as zip_codes from t group by category;
Because your string will be so long, you will need to set
group_concat_max_len to a value larger than its default value.
Why don’t you want these as columns? Well, MySQL has a hard limit of 4,096 columns in a table and I think this limit applies to result sets as well. The limit, though, is further complicated by the storage engine and other settings.. There is enough complication that I just wouldn’t want to dynamically be creating queries that could result in thousands of columns.
You might find that a JSON structure is more convenient than a “mere” string. MySQL also supports that.