sql - SQL 通过匹配 id 查询分组数据并为唯一的 values 添加新列

当前数据集:

USER_ID FEATURE_ID FEATURE_ID_VALUE
123 1 TRUE
321 1 FALSE
123 2 SomeText
321 2 AnotherOne
123 3 MoreText
321 3 EvenMore

我希望能够在 SQL 查询中转换此数据集以生成类似以下内容:

USER_ID FEATURE_ID_1 FEATURE_ID_2 FEATURE_ID_3
123 TRUE SomeText MoreText
321 FALSE AnotherOne EvenMore

我已经研究过使用 Pivots 和 Group By 功能,但我自己无法想出有效的方法。

回答1

您可以使用 PIVOT

SELECT *
FROM   table_name
PIVOT (
  MAX(feature_id_value)
  FOR FEATURE_ID IN (
    1 AS feature_id_1,
    2 AS feature_id_2,
    3 AS feature_id_3
  )
)

其中,对于样本数据:

CREATE TABLE table_name (USER_ID, FEATURE_ID, FEATURE_ID_VALUE) AS
SELECT 123, 1, 'TRUE'       FROM DUAL UNION ALL
SELECT 321, 1, 'FALSE'      FROM DUAL UNION ALL
SELECT 123, 2, 'SomeText'   FROM DUAL UNION ALL
SELECT 321, 2, 'AnotherOne' FROM DUAL UNION ALL
SELECT 123, 3, 'MoreText'   FROM DUAL UNION ALL
SELECT 321, 3, 'EvenMore'   FROM DUAL;

输出:

USER_ID FEATURE_ID_1 FEATURE_ID_2 FEATURE_ID_3
123 TRUE SomeText MoreText
321 FALSE AnotherOne EvenMore

db<>小提琴https://dbfiddle.uk/?rdbms=oracle_21&fiddle=e9ff2220d3742f887ce216ed9a799b65

回答2

你可以使用 case 语句

这是小提琴 https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=025750c52c92080b72b34688a60ba609

select user_id, 
max(case feature_id when 1 then feature_id_value else null end) as Feature_ID_1,
max(case feature_id when 2 then feature_id_value else null end) as Feature_ID_2,
max(case feature_id when 3 then feature_id_value else null end) as Feature_ID_3
 from mytable group by user_id