如何在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屋!