如何在SQL表中使用条件选择列值作为列名 [英] How to select Column value as Column name with conditions in SQL table

问题描述

我是SQL的新手.我有一个客户"表，它看起来像这样.

I'm new to SQL. I have a table 'Customers' and it looks like this.

我想选择性别"作为性别"列，并选择年龄"作为年龄"列.

I would like to select 'Gender' AS Gender Column and 'Age' as Age column which would be like this.

我已经尝试了几种方法，但是仍然没有显示出我的需要.请帮助我.

I've tried several ways but it still doesn't show what I need. Please help me.

推荐答案

一种解决方法是使用条件聚合

One way to go about it is to use conditional aggregation

SELECT name,
       MAX(CASE WHEN field = 'Gender' THEN value END) gender,
       MAX(CASE WHEN field = 'Age' THEN value END) age
  FROM customers
 GROUP BY name

另一种方式(如果您仅对这两列感兴趣)是

The other way (if you're interested only in these two columns) would be

SELECT c1.name, c1.value gender, c2.value age
  FROM customers c1 JOIN customers c2
    ON c1.name = c2.name
   AND c1.field = 'Gender'
   AND c2.field = 'Age';

假设每个名称都同时存在性别和年龄.并非如此，而是使用OUTER JOIN而不是INNER JOIN

Assumption is that both Gender and Age exist for each Name. It it's not the case then use an OUTER JOIN instead of an INNER JOIN like so

SELECT n.name, c1.value gender, c2.value age
  FROM
(
  SELECT DISTINCT name
    FROM customers
) n LEFT JOIN customers c1
    ON n.name = c1.name AND c1.field = 'Gender' 
    LEFT JOIN customers c2
    ON n.name = c2.name AND c2.field = 'Age';

输出:

|   NAME | GENDER | AGE |
|--------|--------|-----|
| Angela | Female |  28 |
|  Davis |   Male |  30 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于如何在SQL表中使用条件选择列值作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！