关于使用UNION ALL转换临时表行数据的SQL技术咨询

咱们来聊聊你这个SQL行转换方案的正确性和优化方向吧：

首先，你想用多次UNION ALL把宽表中的(x1,y1,z1)、(x2,y2,z2)、(x3,y3,z3)分组拆成多行的思路本质是可行的，但你写的代码有几个需要修正的细节：

• 每个UNION ALL分支的列别名必须统一：你第一个分支用a1、b1、c1，后面分支写a2、b2是没用的——SQL会以第一个分支的列名作为最终结果的列名，后续分支的别名不生效，还容易让结果集的含义混淆。正确的做法是把所有分支的列别名统一成a、b、c，还可以额外加一个标识列来区分原分组，比如：

  select date, x1 as a, y1 as b, z1 as c, 'group1' as group_source from @data
  union all
  select date, x2 as a, y2 as b, z2 as c, 'group2' as group_source from @data
  union all
  select date, x3 as a, y3 as b, z3 as c, 'group3' as group_source from @data
  

• 必须保证每个分支的列数、数据类型完全匹配：如果某一行的列类型不兼容，会直接报错，所以如果有不同类型的列，记得用CAST/CONVERT统一类型。

在SQL Server中，专门提供了UNPIVOT运算符来处理这种宽表转长表的需求，比多次UNION ALL更优雅，尤其是数据量大的时候性能更优（只需要扫描原表一次）。针对你的场景，完整示例代码如下：

declare @data table (date varchar(10),x1 int,x2 int,x3 int,y1 int,y2 int,y3 int,z1 numeric(5,2),z2 numeric(5,2),z3 numeric(5,2))
insert into @data values ('2017-05-15',11,12,15,21,31,41,0.1,0.4,0.5)
insert into @data values ('2017-05-16',11,12,15,21,31,41,0.1,0.4,0.5)

-- 使用UNPIVOT实现行转换
select 
    date,
    'group' + replace(group_tag, 'x', '') as group_source,
    a,
    b,
    c
from (
    select 
        date,
        x1, x2, x3,
        y1, y2, y3,
        z1, z2, z3
    from @data
) d
unpivot (
    a for group_tag in (x1, x2, x3)
) upv_x
unpivot (
    b for group_tag_y in (y1, y2, y3)
) upv_y
unpivot (
    c for group_tag_z in (z1, z2, z3)
) upv_z
-- 匹配对应的分组（x1对应y1、z1，以此类推）
where 
    replace(group_tag, 'x', '') = replace(group_tag_y, 'y', '')
    and replace(group_tag, 'x', '') = replace(group_tag_z, 'z', '')

这个方案通过三次UNPIVOT分别拆解x、y、z列组，再通过WHERE条件匹配对应的分组编号，最终得到和UNION ALL完全一致的结果，而且后续如果新增x4、y4、z4这类列，只需要修改UNPIVOT的IN列表即可，扩展性更好。

• 数据类型兼容性：转换后的列必须保证类型一致，比如你的x、y是int，z是numeric，最终a、b、c会自动兼容为numeric（int可以隐式转换为numeric），但如果有字符串和数值混合的情况，必须手动转换类型。
• 性能差异：当表数据量较大时，UNION ALL会多次扫描原表，而UNPIVOT只扫描一次，性能差距会很明显。

内容的提问来源于stack exchange，提问作者Kushant Vyas