Nick Harbdollar

下面是一个查询,它将为您提供所需的结果。它使用CTE创建一个表,其中列出了名称、职业及其在职业中的行号。然后,它使用条件聚合来构建输出表,将CTE结果分组到行号上:

WITH cte AS (
SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation) AS rownum
FROM occs)
SELECT
    MAX(CASE WHEN occupation = 'Actor' THEN name END) AS Actor,
    MAX(CASE WHEN occupation = 'Teacher' THEN name END) AS Teacher,
    MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor
FROM cte
GROUP BY rownum

输出:

Actor   Teacher Doctor
julia   ketty   Jane
meera   null    Zhan

Demo on dbfiddle

为了避免指定列名,需要使用存储过程并创建动态sql查询。

一种方法是 AND 到外面去 CONCAT_WS 并得出 IF 声明 NULL 当变量为空时,因为 无效的 值将被忽略 串接 (见 manual )这样也可以简化内部 串接 到 CONCAT :

set @p1 = '01';
set @p2 = '02';
set @p3 = '03';
set @p4 = '04';
set @p5 = '05';
set @p6 = '';
set @p7 = '07';
set @p8 = '08';
set @p9 = '09';
set @p10 = '010';
set @p11 = '';

SET @VAL =  concat_ws(' AND ',
IF(@p1='',NULL,concat('F1=', @p1)),
IF(@p2='',NULL,concat('F2=', @p2)),
IF(@p3='',NULL,concat('F3=', @p3)),
IF(@p4='',NULL,concat('F4=', @p4)),
IF(@p5='',NULL,concat('F5=', @p5)),
IF(@p6='',NULL,concat('F6=', @p6)),
IF(@p7='',NULL,concat('F7=',@p7)),
IF(@p8='',NULL,concat('F8=', @p8)),
IF(@p9='',NULL,concat('F9=', @p9)),
IF(@p10='',NULL,concat('F10=', @p10)),
IF(@p11='',NULL,concat('F11=', @p11))
);

SET @res = CONCAT_WS(' ','Select ','@VAL');

PREPARE STMT FROM @res;
EXECUTE  STMT;

输出:

F1=01 AND F2=02 AND F3=03 AND F4=04 AND F5=05 AND F7=07 AND F8=08 AND F9=09 AND F10=010

Demo on dbfiddle