Table tranformation for tabular output

[beingalink]

Hi,

I just started with sqlpage and currently hit a roadblock I couldn't overcome. This might rather be a sqlite than a sqlpage question but I hope it's fine to ask here anyway.

I have tabular data which has the following form:

city                year                variable                value
--------            --------            --------                --------
Berlin              1990                var1                    100
Berlin              1991                var1                    120
Berlin              1992                var1                    150
Amsterdam           1990                var1                     80
Amsterdam           1991                var1                     86
Amsterdam           1992                var1                     93
Berlin              1990                var2                     36
Berlin              1991                var2                     42
Berlin              1992                var2                     51
Amsterdam           1990                var2                     29
Amsterdam           1991                var2                     35
Amsterdam           1992                var2                     33

I want to be the output sqlpage table like this:

city                year                var1                    var2
--------            --------            --------                --------
Berlin              1990                100                      36
Berlin              1991                120                      42
Berlin              1992                150                      51
Amsterdam           1990                 80                      29
Amsterdam           1991                 86                      35
Amsterdam           1992                 93                      33

Any ideas how to accomplish that in the most efficient way using sqlpage and sqlite? Thanks for any help in advance!

[lovasoa]

Hello and welcome to SQLPage!

Indeed this is more of a SQL question than a SQLPage question, but I'm glad to answer it anyway :)

You can achieve the desired result using SQL's CASE statement in combination with GROUP BY.

Here’s the query you can use to transform your data into the desired format:

SELECT 
    city,
    year,
    MAX(CASE WHEN variable = 'var1' THEN value END) AS var1,
    MAX(CASE WHEN variable = 'var2' THEN value END) AS var2
FROM your_table
GROUP BY city, year
ORDER BY city, year;

You will need to know in advance the values var1 and var2.

Explanation

1. CASE Statement:

CASE WHEN variable = 'var1' THEN value END: Selects the value for var1.

CASE WHEN variable = 'var2' THEN value END: Selects the value for var2.

2. MAX Function:

SQLite requires an aggregate function when using CASE in a SELECT statement with GROUP BY. If each city-year combination is unique, MAX is safe to use, otherwise use whatever aggregation makes sense for your data.

3. GROUP BY city, year:

Groups the data by city and year to ensure you have one row per combination.

This will render the output table in SQLPage with the desired format.

If the variable names are not known in advance, you will need to use json aggregation functions. This is still possible, but a little bit more complicated. You can find an example in the documentation for the table component: https://sql-page.com/component.sql?component=table (see the example named Dynamic column names in a table)

The common term for what you are trying to do is pivot table; this is the keyword to use if you are looking for more info about it online.

By the way, out of curiosity: what are you building and how did you find out about us?

[beingalink]

Sorry for the late reply. The hint towards the dynamic component with json as input data fixed my issue. The name and number of variables is indeed dynamic in my case which made this hard for me.