在不使用union all的情况下宽数据如何转换成长数据

如图，表1转换成表2，不使用union all，也不使用类似pivot这种函数

在不使用union all或pivot函数的情况下，可以使用以下几种方法将宽数据转换为长数据：

使用case语句：你可以使用case语句将宽数据转换为长数据。例如：

select id,
case when column_name = 'col1' then value end as col1,
case when column_name = 'col2' then value end as col2,
case when column_name = 'col3' then value end as col3
from wide_table;

使用unpivot函数：unpivot函数可以将宽数据转换为长数据。unpivot函数的语法如下：

unpivot(column_name, value
for column_name in (col1, col2, col3))

使用join操作：你可以使用join操作将宽数据转换为长数据。例如：

select a.id, b.column_name, a.value
from wide_table a
join metadata_table b on a.column_id = b.id;

在使用这些方法之前，你可能需要先创建相应的元数据表，以便获取列名等信息。

INSERT INTO table2 (Company, Name, Year, Sales)
SELECT company, Name, 'Sale2013', Sale2013 FROM table1
UNION
SELECT company, Name, 'Sale2014', Sale2014 FROM table1
UNION
SELECT company, Name, 'Sale2015', Sale2015 FROM table1
UNION
SELECT company, Name, 'Sale2016', Sale2016 FROM table1;

仅供参考，望采纳，谢谢。

采用concat_ws() + posexplode()方法，利用pos的位置索引进行一一对应。
假设表格：

select id
     , t.type2  as type
     , t.value1 as value
from t1
         lateral view posexplode(split(concat_ws(',', t1.name, t1.age, t1.gender), ',')) t as pos1, value1
         lateral view posexplode(array('name', 'age', 'gender')) t as pos2, type2
where t.pos1 = t.pos2

如果你的列是年份的话，直接写死年份查询就行了

1、列转行不用union_all是可以了，不过看你统计的年份有多少，年份越多，SQL越长，这里我就按你样例数据来组织SQL了
1.1、先上MySQL5.7的：只有MySQL需要建立样表，不然SQL看起来太复杂了……

SELECT * FROM t_test02;

样例数据（与题主的数据上略有出入）：

转换SQL：

SELECT a.*, y.Year, COALESCE( a1.sale2013, a2.sale2014, a3.sale2015, a4.sale2016 ) Sales
 FROM ( SELECT Company, Name FROM t_test02 ) a CROSS JOIN ( SELECT 2012 + @num AS YEAR, @num := @num + 1 AS col FROM information_schema.`COLUMNS`, ( SELECT @num := 1 ) t WHERE @num < 5 ) y
                                                LEFT JOIN t_test02 a1 ON y.YEAR = 2013 AND a1.company = a.company
                                                LEFT JOIN t_test02 a2 ON y.YEAR = 2014 AND a2.company = a.company
                                                LEFT JOIN t_test02 a3 ON y.YEAR = 2015 AND a3.company = a.company
                                                LEFT JOIN t_test02 a4 ON y.YEAR = 2016 AND a4.company = a.company
 ORDER BY a.Company, y.year;

转换结果：

1.2、Oracle的：

WITH t AS ( -- 源表模拟
        SELECT 'Apple' Company, '苹果' AS NAME,5000 AS Sale2013, 5050 AS Sale2014, 6000 AS Sale2015, 6050 AS Sale2016 FROM dual UNION ALL
        SELECT 'Google','谷歌',3500,3600,3700,3900 FROM dual UNION ALL
        SELECT 'Facebook','脸书',2300,2900,3100,4000 FROM dual )
    , y AS ( -- 年份生成
        SELECT 2012 + ROWNUM AS YEAR FROM dual CONNECT BY ROWNUM < 5 )
SELECT a.*, y.*, COALESCE( a1.sale2013, a2.sale2014, a3.sale2015, a4.sale2016 ) Sales
  FROM y CROSS JOIN ( SELECT company, name FROM t ) a
          LEFT JOIN t a1 ON y.YEAR = 2013 AND a1.company = a.company
          LEFT JOIN t a2 ON y.YEAR = 2014 AND a2.company = a.company
          LEFT JOIN t a3 ON y.YEAR = 2015 AND a3.company = a.company
          LEFT JOIN t a4 ON y.YEAR = 2016 AND a4.company = a.company
 ORDER BY a.Company, y.year;

结果同上

1.3、PostGresQL的：

WITH t AS ( -- 源表模拟
        SELECT * FROM ( VALUES ( 'Apple', '苹果',5000,5050,6000,6050 ), ( 'Google','谷歌',3500,3600,3700,3900 ), ( 'Facebook','脸书',2300,2900,3100,4000 ) ) AS t( Company,Name,Sale2013,Sale2014,Sale2015,Sale2016 ) )
    , y AS ( -- 年份生成
        SELECT * FROM ( VALUES ( 2013 ), ( 2014 ), ( 2015 ), ( 2016 ) ) AS t( Year ) )
SELECT a.*, y.*, COALESCE( a1.sale2013, a2.sale2014, a3.sale2015, a4.sale2016 ) Sales
  FROM y CROSS JOIN ( SELECT company, name FROM t ) a
          LEFT JOIN t a1 ON y.YEAR = 2013 AND a1.company = a.company
          LEFT JOIN t a2 ON y.YEAR = 2014 AND a2.company = a.company
          LEFT JOIN t a3 ON y.YEAR = 2015 AND a3.company = a.company
          LEFT JOIN t a4 ON y.YEAR = 2016 AND a4.company = a.company
 ORDER BY a.Company, y.year;

您可以使用下面的步骤来将宽数据转换成长数据，而不使用UNION ALL或PIVOT函数：

1 使用CROSS JOIN将数据水平拼接在一起。
例如：

SELECT t1.id, 'name' as type, t1.name as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 1

UNION ALL

SELECT t1.id, 'age' as type, t1.age as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 2

UNION ALL

SELECT t1.id, 'gender' as type, t1.gender as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 3

2 使用ORDER BY语句按照id和type的值排序。
例如：

SELECT t1.id, 'name' as type, t1.name as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 1

UNION ALL

SELECT t1.id, 'age' as type, t1.age as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 2

UNION ALL

SELECT t1.id, 'gender' as type, t1.gender as value
FROM table1 t1
CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
WHERE t2.n = 3
ORDER BY id, type

3 使用ROW_NUMBER()函数生成行号。
例如：

SELECT ROW_NUMBER() OVER (ORDER BY id, type) as row_num, id, type, value
FROM (
  SELECT t1.id, 'name' as type, t1.name as value
  FROM table1 t1
  CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
  WHERE t2.n = 1
  UNION ALL
  SELECT t1.id, 'age' as type, t1.age as value
  FROM table1 t1
  CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
  WHERE t2.n = 2
  UNION ALL
  SELECT t1.id, 'gender' as type, t1.gender as value
  FROM table1 t1
  CROSS JOIN (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n) t2
  WHERE t2.n = 3
) t
ORDER BY id, type

请注意，在使用ROW_NUMBER()函数之前，必须先使用CROSS JOIN将数据拼接在一起，并使用ORDER BY语句按照id和type的值排序。

最后，您可以使用SELECT子句中的别名（例如row_num）来选择行号列，并使用其他列（例如id，type，value）来选择其他列。

这样就可以得到类似于表2的结果了。

希望这可以帮到您！望采纳。

在不使用 UNION ALL 和 PIVOT 之类的函数的情况下，将宽数据转换成长数据可以通过两个步骤来实现：

将宽表中的每一列数据拆分成一行。可以使用 CROSS JOIN 或者 UNNEST 操作来实现。

将拆分后的行数据中的每一列与原始列的列名和行数据的其它列进行组合。可以使用 LATERAL JOIN 或者 JOIN 操作来实现。

举个例子，假设有一个宽表 wide_table，它有三列：id、col1 和 col2。

可以使用下面的 SQL 语句将宽表转换成长数据：

WITH wide_data AS (
    SELECT id, col1, col2 FROM wide_table
),
split_data AS (
    SELECT id, unnest(array[col1, col2]) as value FROM wide_data
)
SELECT id, value, row_number() OVER (PARTITION BY id ORDER BY value) as position
FROM split_data

这样会得到一个长表，它有三列：id、value 和 position。其中，position 表示原始列的列名。

需要注意的是，上面的 SQL 语句是使用 UNNEST 函数来实现拆分列数据的，这个函数不是所有数据库都支持，如果你使用的数据库不支持 UNNEST ，可以使用其它数组操作函数来替代它
希望对你有帮助，望采纳。