SQL Server - Rows into Columns (Grouped)

Question

I've tried to use PIVOT to combine some of my rows into columns, but the thing is, I have to group them, and I couldn't figure out how would I do it.

SQL query:

select  res.tipo,
            it.itemname as item,
            sum(resi.quantity) as qt
from    Reserve as res inner join
            ReserveItems as resi on res.id_reserve = resi.id_reserve inner join
            Items as it on resi.defindex = it.defindex
where   res.situacao = 3
group by res.tipo, it.id, it.itemname
order by tipo, it.id

Results:

tipo   item                                                                                                 qt
------ ---------------------------------------------------------------------------------------------------- -----------
0      Mann Co. Supply Crate Key                                                                            6
0      Tour of Duty Ticket                                                                                  10
0      Reinforced Robot Emotion Detector                                                                    5
0      Reinforced Robot Bomb Stabilizer                                                                     1
0      Battle-Worn Robot Taunt Processor                                                                    3
0      Battle-Worn Robot KB-808                                                                             22
0      Battle-Worn Robot Money Furnace                                                                      19
1      Mann Co. Supply Crate Key                                                                            41
1      Tour of Duty Ticket                                                                                  31
1      Pristine Robot Currency Digestor                                                                     1
1      Pristine Robot Brainstorm Bulb                                                                       2
1      Reinforced Robot Emotion Detector                                                                    32
1      Reinforced Robot Humor Supression Pump                                                               45
1      Reinforced Robot Bomb Stabilizer                                                                     39
1      Battle-Worn Robot Taunt Processor                                                                    69
1      Battle-Worn Robot KB-808                                                                             78
1      Battle-Worn Robot Money Furnace                                                                      109

Desired Results:

item                                        qt_1        qt_0
------------------------------------------  ----------  --------
Mann Co. Supply Crate Key                   41          6
Tour of Duty Ticket                         27          6
Pristine Robot Currency Digestor            1           0
Pristine Robot Brainstorm Bulb              2           0
Reinforced Robot Emotion Detector           32          5
Reinforced Robot Humor Supression Pump      45          0
Reinforced Robot Bomb Stabilizer            39          1
Battle-Worn Robot Taunt Processor           89          3
Battle-Worn Robot KB-808                    92          16
Battle-Worn Robot Money Furnace             109         19

Is there any possibility to do this in a easy way? (without using #temp, and inserting/updating values). Using pivot would be the best for me =)

EDIT:

The solution was based on JanR answer. Thanks again, man!

select  it.itemname as item,
            sum(case when res.tipo = 0 then resi.quantity else 0 end) as qt_compra,
            sum(case when res.tipo = 1 then resi.quantity else 0 end) as qt_venda
from    Reserve as res inner join
            ReserveItems as resi on res.id_reserve = resi.id_reserve inner join
            Items as it on resi.defindex = it.defindex
where   res.situacao = 3
group by it.id, it.itemname
order by it.id

Answer 1

Not sure if this will answer your question, however I found that you are including tipo in your group by clause, this will cause the records to be split as for instance 'Mann Co. Supply Crate Key' can have a tipo of 0 or 1 according to your information. And this will group them by 0 Mann Co. Supply Crate Key & a second group of 1 Mann Co. Supply Crate Key.

EDIT: looking at your query you probably want something like this:

select  
            it.itemname as item,
            sum(case when resi.tipo = 0 then resi.quantity else 1 end) as qty_0,
            sum(case when resi.tipo = 1 then resi.quantity else 0 end) as qty_1
from    Reserve as res inner join
            ReserveItems as resi on res.id_reserve = resi.id_reserve inner join
            Items as it on resi.defindex = it.defindex
where   res.situacao = 3
group by it.itemname
order by tipo, it.id

Keep in mind that without knowing the table structures it's a bit hard :)