There are many different ways that you can get the result. Multiple JOINs, unpivot/pivot or CASE with aggregate.
They all have pros and cons, so you'll need to decide what will work best for your situation.
Multiple Joins - now you've stated that you will always have 2 rows for each day - one for apple and orange. When joining on the table multiple times your need some sort of column to join on. It appears the column is timestamp but what happens if you have a day that you only get one row. Then the INNER JOIN solution provided by @Becuzz won't work because it will only return the rows with both entries per day. LeYou could use multiple JOINs using a FULL JOIN which will return the data even if there is only one entry per day:
select
[Timestamp] = Coalesce(a.Timestamp, o.Timestamp),
ApplesNumOffered = a.[NumOffered],
ApplesNumTaken = a.[NumTaken],
ApplesNumAbandoned = a.[NumAbandoned],
ApplesNumSpoiled = a.[NumSpoiled],
OrangesNumOffered = o.[NumOffered],
OrangesNumTaken = o.[NumTaken],
OrangesNumAbandoned = o.[NumAbandoned],
OrangesNumSpoiled = o.[NumSpoiled]
from
(
select timestamp, numoffered, NumTaken, numabandoned, numspoiled
from myTable
where FruitType = 'Apple'
) a
full join
(
select timestamp, numoffered, NumTaken, numabandoned, numspoiled
from myTable
where FruitType = 'Orange'
) o
on a.Timestamp = o.Timestamp
order by [timestamp];
See SQL Fiddle with Demo. Another issue with multiple joins is what if you have more than 2 values, you'll need an additional join for each value.
If you have a limited number of values then I'd suggest using an aggregate function and a CASE expression to get the result:
SELECT
[timestamp],
sum(case when FruitType = 'Apple' then NumOffered else 0 end) AppleNumOffered,
sum(case when FruitType = 'Apple' then NumTaken else 0 end) AppleNumTaken,
sum(case when FruitType = 'Apple' then NumAbandoned else 0 end) AppleNumAbandoned,
sum(case when FruitType = 'Apple' then NumSpoiled else 0 end) AppleNumSpoiled,
sum(case when FruitType = 'Orange' then NumOffered else 0 end) OrangeNumOffered,
sum(case when FruitType = 'Orange' then NumTaken else 0 end) OrangeNumTaken,
sum(case when FruitType = 'Orange' then NumAbandoned else 0 end) OrangeNumAbandoned,
sum(case when FruitType = 'Orange' then NumSpoiled else 0 end) OrangeNumSpoiled
FROM myTable
group by [timestamp];
See SQL Fiddle with Demo. Or even using PIVOT/UNPIVOT like @M.Ali has. The problem with these are what if you have unknown values - meaning more than just Apple and Orange. You are left with using dynamic SQL to get the result. Dynamic SQL will create a string of sql that needs to be execute by the engine:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(FruitType + col)
from
(
select FruitType
from myTable
) d
cross apply
(
select 'NumOffered', 0 union all
select 'NumTaken', 1 union all
select 'NumAbandoned', 2 union all
select 'NumSpoiled', 3
) c (col, so)
group by FruitType, Col, so
order by FruitType, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT TimeStamp,' + @cols + '
from
(
select TimeStamp,
new_col = FruitType+col, value
from myTable
cross apply
(
select ''NumOffered'', NumOffered union all
select ''NumTaken'', NumOffered union all
select ''NumAbandoned'', NumOffered union all
select ''NumSpoiled'', NumOffered
) c (col, value)
) x
pivot
(
sum(value)
for new_col in (' + @cols + ')
) p '
exec sp_executesql @query;
See SQL Fiddle with Demo
All versions give the result:
| timestamp | AppleNumOffered | AppleNumTaken | AppleNumAbandoned | AppleNumSpoiled | OrangeNumOffered | OrangeNumTaken | OrangeNumAbandoned | OrangeNumSpoiled |
|---------------------------|-----------------|---------------|-------------------|-----------------|------------------|----------------|--------------------|------------------|
| January, 01 2015 00:00:00 | 55 | 12 | 0 | 0 | 12 | 5 | 0 | 1 |
| January, 02 2015 00:00:00 | 21 | 6 | 2 | 1 | 60 | 43 | 0 | 0 |
| January, 03 2015 00:00:00 | 49 | 17 | 2 | 1 | 109 | 87 | 12 | 1 |
| January, 04 2015 00:00:00 | 6 | 4 | 0 | 0 | 53 | 40 | 0 | 1 |
| January, 05 2015 00:00:00 | 32 | 14 | 1 | 0 | 41 | 21 | 5 | 0 |
| January, 06 2015 00:00:00 | 26 | 24 | 0 | 1 | 97 | 30 | 10 | 1 |
| January, 07 2015 00:00:00 | 17 | 9 | 2 | 0 | 37 | 27 | 0 | 4 |
| January, 08 2015 00:00:00 | 83 | 80 | 3 | 0 | 117 | 100 | 5 | 1 |
