Sum across columns and rows

Question

Consider a table like this

table
+--------+---------+-----------+---------+-----------+
| BookId | ItemId1 | Quantity1 | ItemId2 | Quantity2 |
+--------+---------+-----------+---------+-----------+
|      1 |       1 |         2 |       2 |         1 |
|      1 |       3 |         1 |       2 |         1 |
|      2 |       1 |         1 |       2 |         1 |
+--------+---------+-----------+---------+-----------+

Now I want to get the sum of columns quantity for each item grouped by book. How can I take the sum across different columns then? right now I use an awkward solution like building a temporary table and then querying this one, but it must be possible in a more elegant way!?

select
    BookId 'BookId',
    ItemId1 'ItemId',
    Quantity1 'Quantity'
into #temptab
from table
union all
select
    BookId,
    ItemId2,
    Quantity2
from table

and after that

select 
    BookId,
    ItemId,
    sum(Quantity)
from #temptab
group by ItemId, BookId

How can I get rid of this intermediate step?

Desired output:

+--------+--------+----------+
| BookId | ItemId | Quantity |
+--------+--------+----------+
|      1 |      1 |        2 |
|      1 |      3 |        1 |
|      1 |      2 |        2 |
|      2 |      1 |        1 |
|      2 |      2 |        1 |
+--------+--------+----------+

Answer 1

Use cross apply with table valued constructor to unpivot the data then find sum per bookid and item.

This will avoid your intermediate step

SELECT BookId,
       item,
       Sum(quantity)
FROM   Youratble
       CROSS apply (VALUES(Quantity1,ItemId1),
                          (Quantity2,ItemId2))cs(quantity, item)
GROUP  BY BookId,
          item 

As mentioned by Mikael Eriksson for sql server 2005 use this

SELECT BookId,
       item,
       Sum(quantity)
FROM   Youratble
       cross apply 
            (select Quantity1, ItemId1 
             union all 
             select Quantity2, ItemId2) as cs(quantity, item)
GROUP  BY BookId,
          item