当前数据集：
| 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