如何在 SQL 级别使用 UNION 平铺 select 语句的输出? [英] How to flat an output of a select statement with UNION on the SQL level?

问题描述

这是一个选择语句:

SELECT aField FROM myTable
  WHERE ID = pId and Name = 'C'
UNION ALL
SELECT aField  FROM myTable
  WHERE ID = pId and Name = 'CC'
UNION ALL
SELECT aField  FROM myTable
  WHERE ID = pId and Name = 'S'
UNION ALL
SELECT aField  FROM myTable
  WHERE ID = pId and Name = 'SC'
UNION ALL
SELECT aField  FROM myTable
  WHERE ID = pId and Name = 'R'
UNION ALL
SELECT aField  FROM myTable
  WHERE ID = pId and Name = 'RC';

是一种将输出数据放在平面数据集中而不是列表中的方法吗?

Is a way to have the output data in a flat data set instead of a list?

推荐答案

使用条件聚合:

SELECT MAX(CASE WHEN Name = 'C' THEN RelativePath END) AS C,
       MAX(CASE WHEN Name = 'CC' THEN RelativePath END) AS CC,
       MAX(CASE WHEN Name = 'S' THEN RelativePath END) AS S,
       MAX(CASE WHEN Name = 'SC' THEN RelativePath END) AS SC,
       MAX(CASE WHEN Name = 'R' THEN RelativePath END) AS R,
       MAX(CASE WHEN Name = 'RC' THEN RelativePath END) AS RC
FROM myTable
WHERE ID = pId;

这里假设每个名称最多只有一个值.

This assumes that you have at most one value for each name.

这篇关于如何在 SQL 级别使用 UNION 平铺 select 语句的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！