将动态列逆透视到行 - 最后一列中缺少数据

我有很多如下所示的表，每个表的列名称都不同。

id  city  aaaa  bbbb  cccc  dddd  eeee  ffff  gggg  hhhh  iiii  jjjj  kkkk  llll  mmmm   
--  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----  ----
1   LAX    0.0   0.0   1.0   2.5   0.0   3.0   0.0   0.0   1.0   0.0   1.6  0.0  1.1
2   SFO    1.7   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  0.0  2.4
3   NYC    0.0   1.0   0.0   0.0   4.0   0.0   0.0   0.0   0.0   2.0   0.0  0.0  1.3
  

我希望得到上面示例表的如下结果：

id city item  qty
-- ---- ----  ----
1  LAX  cccc   1.0
1  LAX  dddd   2.5
1  LAX  ffff   3.0
1  LAX  iiii   1.0
1  LAX  kkkk   1.6
1  LAX  mmmm   1.1  (missing from result)
2  SFO  aaaa   1.7
2  SFO  mmmm   2.4  (missing from result)
3  NYC  bbbb   1.0
3  NYC  eeee   4.0
3  NYC  jjjj   2.0
3  NYC  mmmm   1.3  (missing from result)
  

我尝试从下面的链接修改查询，但无法获取所有记录。它仅返回 9 条记录，并且缺少“llll”和“mmmm”列中的数据。我尝试过使用不同的表，但它最多收集了前 10 列。我了解基本的 SQL，但这太复杂了，无法解决。请帮我！

将动态列转置为行 https://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows

    (Copied from above link page and I modified a little)

    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
        'select id, city, ''',
        c.column_name,
        ''' as item, ',
        c.column_name,
        ' as qty 
        from test1
        where ',
        c.column_name,
        ' > 0'
        ) SEPARATOR ' UNION ALL '
    ) INTO @sql
    FROM information_schema.columns c
    where c.table_name = 'test1'
        and c.column_name not in ('id','city')
    order by c.ordinal_position;

    SET @sql 
        = CONCAT('select id, city, item, qty
        from
        (', @sql, ') x  order by id');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;