How to separate data into different column sql?

Question

Column

inv_num       scn

MI1001        160008

CI1002        160008

VI1003        160008

MI is for Misc, CI is for Cargo, VI is for Vessel.

I need the sql result to show like

scn           Misc       Cargo      Vessel

160008        MI1001     CI1002     VI1003

I'm new to this kind of thing so not sure how to do the query.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I would do this with conditional aggregation:

select scn,
       max(case when inv_num like 'MI%' then inv_num end) as Misc,
       max(case when inv_num like 'CI%' then inv_num end) as Cargo,
       max(case when inv_num like 'VI%' then inv_num end) as Vessel
from t
group by scn;

How to separate data into different column sql?

Sign up