如何在Ignition中用单SELECT语句实现SQL表行列转置？

需求说明 #

需要将如下结构的1行7列SQL表：

转置为7行2列的目标表：

此前尝试过创建临时表插入数据的方式实现转置，但由于Ignition仅返回第一个SELECT语句的结果，该方法无效。最初因无分组列难以使用UNPIVOT语法，添加占位列后问题解决，现需仅通过SELECT语句完成转置以适配Ignition环境。

原临时表方法（不适配Ignition） #

此方法因涉及多步操作，Ignition仅会返回第一个SELECT * FROM mytable的结果，无法得到最终转置数据：

-- 创建测试表
create table mytable
(
    [Fault1] int, 
    [Fault2] int, 
    [Fault3] int
);

-- 插入测试数据
insert into mytable ([Fault1], [Fault2], [Fault3])
values (1, 2, 3);

-- 查询原表数据（Ignition仅返回此结果）
select * 
from mytable

-- 创建转置结果表
create table pvt
(
    [Fault] varchar(25), 
    [FaultCount] int
); 

-- 插入转置数据
insert into pvt values ('Fault1', (select Fault1 from mytable)); 
insert into pvt values ('Fault2', (select Fault2 from mytable)); 
insert into pvt values ('Fault3', (select Fault3 from mytable)); 

-- 转置结果（Ignition不会执行到这一步）
select * from pvt

适配Ignition的UNPIVOT解决方案 #

通过添加一个占位列提供分组依据，最终用单条SELECT语句直接返回转置结果：

-- 创建带占位列的测试表
create table mytable
(
    [Fault0] varchar(25), -- 占位列，用于UNPIVOT分组，值可任意填写
    [Fault1] int, 
    [Fault2] int, 
    [Fault3] int
);

-- 插入测试数据
insert into mytable ([Fault0], [Fault1], [Fault2], [Fault3])
values ('placeholder', 1, 2, 3);

-- 执行转置并返回结果（Ignition会直接返回此查询的结果）
select FaultName as NameCol, FaultCount as IntCol
from (
  select Fault0, Fault1, Fault2, Fault3
  from mytable
) b 
unpivot ( 
  FaultCount for FaultName in (Fault1, Fault2, Fault3)
) as unpvt;

方案说明 #

• 占位列Fault0的作用是为UNPIVOT提供固定的分组维度，解决无ID列无法使用UNPIVOT的问题
• 最终仅通过单条SELECT语句返回转置结果，完全适配Ignition的执行特性

内容的提问来源于stack exchange，提问作者J Hyde