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 |
+--------+--------+----------+
Aucun commentaire:
Enregistrer un commentaire