为相同ID的多行选择不同的值 [英] SELECT distinct values for multiple rows of same ID

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

ID | FIELD_NAME   | VALUE
23 |  sign_up     |  yes
23 |  first_name  |  Fred
23 |  street      |  Barber Lane
24 |  sign_up     |  no
24 |  first_name  |  Steve
24 |  street      |  Camaro St.
25 |  sign_up     |  yes
25 |  first_name  |  Larry
25 |  street      |  Huckleberry Ave

我想运行一个查询，该查询将选择唯一ID和值作为命名列，这样看起来就这样:

I want to run a query that will select unique ID's and the values as named columns so it would appear like so:

ID   |   SIGN_UP   | FIRST_NAME  |  STREET           |
23   |     yes     |    Fred     |  Barber Lane      |
24   |     no      |    Steve    |  Camaro St.       |
25   |     yes     |    Larry    |  Huckleberry Ave. |

任何帮助将不胜感激！

推荐答案

您可以使用以下简单解决方案:

You can use this simple solution:

SELECT DISTINCT
    a.id,
    b.value AS SIGN_UP,
    c.value AS FIRST_NAME,
    d.value AS STREET
FROM tbl a
LEFT JOIN tbl b ON a.id = b.id AND b.field_name = 'sign_up'
LEFT JOIN tbl c ON a.id = c.id AND c.field_name = 'first_name'
LEFT JOIN tbl d ON a.id = d.id AND d.field_name = 'street'

为了安全起见，我进行了联接LEFT JOIN，因为我不知道id是否可以具有 missing 字段，在这种情况下，它们将在我们的字段中显示为NULL派生列.

Just to be safe, I made the joins LEFT JOIN's because I do not know if an id can have missing fields, in which case they will show up as NULL in our derived columns.

SQL小提琴演示

这篇关于为相同ID的多行选择不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！