user3090790 user3090790 - 7 months ago 14
SQL Question

How to separate data from one column result into multiple columns?

Below is sample data and expected results:

Table 1 :

out_number AMPS VA Feed
1 0.00 0 A
2 0.10 0 A
3 0.15 0 A
4 0.30 0 B
5 0.40 0 B
6 0.80 0 B


Query Results:

out_number AMPS VA Feed out_number AMPS VA Feed
1 0.00 0 A 4 0.30 0 B
2 0.10 0 A 5 0.40 0 B
3 0.15 0 A 6 0.80 0 B


My current query:

select d1.*, d2.out_number, d2.AMPS,d2.VA,d2.Feed
from #tbl1 d1 ,
#tbl1 d2
where d1.Feed = 'A' and d2.Feed = 'B'


But getting below result

enter image description here

Answer

Try this, create two sub sets Feed = 'A' and Feed = 'B' and join them with unique column (i used a row_number() here).

SELECT  *
FROM    (   SELECT row_number() over(order by AMPS) row_no, * 
            FROM @table1 t1 where t1.Feed = 'A' ) T1
    INNER JOIN (SELECT row_number() over(order by AMPS) row_no, * 
                FROM @table1 t2 where t2.Feed = 'B' ) T2
        ON T1.row_no = T2.row_no