How to create 2 columns using data from 1 column and merging them-CodePudding

I'm facing the some problems in big query, the single column could not separate into 2 columns. I want the column index with 8 and 10 to be new columns called universal_id and project_id using the value in the column "value".

My current table is:

user_id | index | value
a.      | 1.    | 123
b.      | 8.    | 456
c.      | 10.   | 12.60
b.      | 10.   | 789

I want the result to be this:

user_id | project_id | universal_id | 
a       | NA         | NA
b.      | 789        | 456
c.      | 12.60      | NA

I have tried this, but it does not work. I searched a lot of places, and could find the answer I am looking for. Any help would be greatly appreciated. Thank you in advance!!!

select user_id,
case when index = 8 then value else null end as universal_id,
case when index = 10 then value else null end as ps_project_id

from test_1

CodePudding user response：

You may use conditional aggregation here:

SELECT
    user_id,
    MAX(CASE WHEN index = 10 THEN value END) AS project_id,
    MAX(CASE WHEN index = 8  THEN value END) AS universal_id
FROM test_1
GROUP BY user_id;

CodePudding user response：

Consider below approach

select * from your_table
pivot (
  min(value) for case index 
    when 10 then 'project_id' 
    when 8 then 'universal_id' 
  end in ('project_id', 'universal_id')
)

if applied to sample data in your question - output is