在SQL中创建详尽的案例列 [英] Creating exhaustive case columns in SQL

问题描述

我正在尝试生成一列性别组合"，以创建互动条件的详尽类别，如下表所示.有没有一种简单的方法可以在SQL(Microsoft服务器)中做到这一点?

I am trying to generate a column "Gender Combinations" that creates exhaustive categories of interaction terms like in the table below. Is there an easy way to do this in SQL (microsoft server)?

+--------------+--------------+--------------+---------------------+
| EMP 1 Gender | EMP 2 Gender | Emp 3 Gender | Gender Combinations |
+--------------+--------------+--------------+---------------------+
| Male         |              |              | 1 Male              |
| Female       |              |              | 1 Female            |
|              | Male         |              | 1 Male              |
|              | Female       |              | 1 Female            |
|              |              | Male         | 1 Male              |
|              |              | Female       | 1 Female            |
| Male         | Female       |              | 1 Male, 1 Female    |
|              | Male         | Female       | 1 Male, 1 Female    |
| Male         |              | Female       | 1 Male, 1 Female    |
|              | Female       | Male         | 1 Male, 1 Female    |
| Female       |              | Male         | 1 Male, 1 Female    |
| Female       | Male         |              | 1 Male, 1 Female    |
|              | Female       | Male         | 1 Male, 1 Female    |
| Male         |              | Female       | 1 Male, 1 Female    |
|              | Male         | Female       | 1 Male, 1 Female    |
| Male         | Male         | Female       | 1 Male, 1 Female    |
| Male         | Female       | Male         | 2 Males, 1 Female   |
| Female       | Male         | Male         | 1 Female, 2 Males   |
| Female       | Female       | Male         | 2 Females, 1 Male   |
| Female       | Male         | Female       | 2 Females, 1 Male   |
| Male         | Female       | Female       | 2 Females, 1 Male   |
| Male         | Male         | Male         | 3 Males             |
| Female       | Female       | Female       | 3 Females           |
+--------------+--------------+--------------+---------------------+

推荐答案

我会使用apply:

select t.*, g.gender_combination
from t cross apply
     (select ((case when num_males = 1 then '1 Male; '
                    when num_males > 1 then cast(varchar(255), num_males) + ' Males; '
                    else ''
              ) +
              (case when num_females = 1 then '1 Female; '
                    when num_females > 1 then cast(varchar(255), num_females) + ' Females; '
                    else ''
              )
             ) as gender_combination
      from (select sum(case when gender = 'Male' then 1 else 0 end) as num_males,
                   sum(case when gender = 'Female' then 1 else 0 end) as num_females                   
            from values (t.emp1_gender), (t.emp2_gender), (t.emp3_gender) as v(gender)
           ) v
     ) g;

这篇关于在SQL中创建详尽的案例列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！