Haye Haye - 4 years ago 83
SQL Question

How to separate data into different column sql?

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.

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download