SQL query group data by matching id and add new columns for unique values-CodePudding

Current dataset:

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

I want to be able to transform this dataset inside a SQL query to produce something like the following:

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

I've looked into using Pivots and Group By functions but can't come up with something effective myself.

CodePudding user response：

You can use 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
  )
)

Which, for the sample data:

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;

Outputs:

USER_ID FEATURE_ID_1 FEATURE_ID_2 FEATURE_ID_3

123 TRUE SomeText MoreText

321 FALSE AnotherOne EvenMore

db<>fiddle here

CodePudding user response：

you could use a case statement

here is the fiddle 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