Mysql, Reshape Data from Long/Tall to Wide

Mysql, reshape data from long / tall to wide

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

Something like:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

Reshape data long to wide in MySQL

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.

Mysql convert table from long format to wide format

Try this:

insert into goodTable 
select
bt.id,
(select bt1.value from badTable bt1 where bt1.info = 'firstname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'lastname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'phone' and bt1.id = bt.id)
from
 badTable bt
group by id ;

Working fiddle here: http://sqlfiddle.com/#!2/45f29e/2

SQL: Convert a Narrow Table to a Wide Table

You can do it using PIVOT query.

You need to decide in advance how wide a result should be - I mean how many "groups" of columns (name,title,phone) a final result should have.


The below example divides the whole table into 3 groups of (name+title+phone) columns:

WITH my_data AS (
    SELECT LOCATION, User_Name, Title, Phone,
           trunc( rn / 3 ) as group_number,
           rn - 3 * trunc( rn / 3 ) as rownum_within_group
    FROM (
        SELECT t.* ,
              row_number() over (partition by location order by user_name ) - 1 As rn
        FROM table1 t
    ) t
    -- ORDER BY Location, user_name
)
SELECT * 
FROM my_data
PIVOT (
   MAX( User_name) As user_name,
   MAX( Title ) as Title,
   MAX( Phone ) As Phone
   FOR rownum_within_group IN (0 as G0,1 As G1 ,2 As G2)
)
ORDER BY 1,2;

and a sample result for slightly modified data from your example (I've added additional "Batman2"+"Batman3"+"Batman4" entries) is:

LOCATION       GROUP_NUMBER G0_USER_NAME    G0_TITLE    G0_PHONE    G1_USER_NAME    G1_TITLE    G1_PHONE    G2_USER_NAME    G2_TITLE    G2_PHONE
Backyard                 0  Batman          Manager     9112        Tiger Woods     Worker      34-45           
Baseball Park            0  Batman          Manager     9112        Jane Doe        Worker      23-34           
Living Room              0  Batman          Manager     9112        Batman2         Manager     9112        Batman3         Manager     9112
Living Room              1  Batman4         Manager     9112        Joe Schmo       Worker      12-23   

There are 5 users in location Living Room, so this location is divided into two rows in the results set, the first row has 3 users, and the second row has 2 users (see two last rows in the example above).

Convert data from wide format to long format in SQL

Provided your score columns are fixed and you require no aggregation, you can use multiple SELECT and UNION ALL statements to generate the shape of data you requested. E.g.

SELECT [VAR1], [VAR2], [VarName] = 'Score1', [Value] = [Score1]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score2', [Value] = [Score2]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score3', [Value] = [Score3]
FROM [dbo].[UnknownMe]

SQL Fiddle: http://sqlfiddle.com/#!6/f54b2/4/0

Reshape the data from long to wide format

The crucial point here is to identify which rows belong to which group. The answers by Ronak and akrun both use rleid(Service_tier) assuming that a change in Service_tier indicate the begin of a new group.

This might be suggested by the sample dataset but cannot be taken as guaranteed. IMHO, Service_tier is rather an attribute than a key. As a matter of fact, the OP is testing for NN == EE in his code snippet to switch to a new group.

In the data.table solutions below, grouping is determined by cumsum(shift(NN == EE, fill = TRUE)) which tests for equality fo NN and EE, lags the result to the next row where the next group starts, and enumerates the groups by counting TRUE using cumsum().

In the simplified version (without reshaping), the hops are aggregated by the toString() function:

library(data.table)
setDT(d)[, .(SN = first(SN), hops = toString(NN), Service_tier = first(Service_tier)), 
  by = .(grp = cumsum(shift(NN == EE, fill = TRUE)))][]

   grp SN       hops Service_tier
1:   1  A       B, C      economy
2:   2  P    Q, S, R      regular
3:   3  H I, J, K, L      economy

For reshaping from long to wide format, dcast() is used:

library(data.table)
library(magrittr)   # piping used to improve readability
w <- setDT(d)[, .(SN = first(SN), hops = NN, Service_tier = first(Service_tier)), 
  by = .(grp = cumsum(shift(NN == EE, fill = TRUE)))] %>% 
  dcast(grp + ... ~ rowid(grp, prefix = "hop"), value.var = "hops", fill = "")  %>%  
  setcolorder(c(1:2, 4:ncol(.), 3))

w

   grp SN hop1 hop2 hop3 hop4 Service_tier
1:   1  A    B    C                economy
2:   2  P    Q    S    R           regular
3:   3  H    I    J    K    L      economy

setcolorder() is used to rearrange columns in the order expected by the OP. This is done in-place, i.e., without copying the whole data object.

Data

library(data.table)

d <- fread("SN NN EE Service_tier
A  B  C  economy
B  C  C  economy
P  Q  R  regular
Q  S  R  regular
S  R  R  regular
H  I  L  economy
I  J  L  economy
J  K  L  economy
K  L  L  economy")

Redshift SQL - Tall/long to Wide Format

You want to sum or count the CASE expressions, e.g.

SELECT
    country,
    SUM(CASE WHEN date BETWEEN '2021-10-01' AND '2021-10-31'
             THEN subscribers ELSE 0 END) AS "Oct-21",
    SUM(CASE WHEN date BETWEEN '2021-11-01' AND '2021-11-30'
             THEN subscribers ELSE 0 END) AS "Nov-21",
    SUM(CASE WHEN date BETWEEN '2021-12-01' AND '2021-12-31'
             THEN subscribers ELSE 0 END) AS "Dec-21"
FROM source_table
GROUP BY country;

---

Related Topics