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.

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Gurwinder Singh, Feb 07 '17 at 03:21

score 4 · Accepted Answer · answered Feb 07 '17 at 03:21

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;

score 0 · Answer 2 · answered Feb 07 '17 at 09:09

using Pivot also we can achieve

Sample Data

Declare @Table1 TABLE 
    (inv_num varchar(6), scn int)
;

INSERT INTO @Table1
    (inv_num, scn)
VALUES
    ('MI1001', 160008),
    ('CI1002', 160008),
    ('VI1003', 160008)
;

Script

Select scn,[1] AS Misc,[2] As Cargo,[3] As Vessel  from (
select 
inv_num, 
scn,
ROW_NUMBER()OVER(PARTITION BY scn ORDER BY (SELECT NULL))RN 
        from @Table1)T
PIVOT (MAX(inv_num) FOR RN IN ([1],[2],[3]))PVT

How to separate data into different column sql?

2 Answers2