有许多不同的方法可以获得结果。多个 JOIN、unpivot/pivot 或带有聚合函数的 CASE 都有优缺点，因此您需要决定哪种方法最适合您的情况。
多个 JOIN - 现在您已经说明每天都会有 2 行 - 苹果和橙子。当多次连接表时，您需要某种列进行连接。似乎该列是 timestamp，但如果您只有一行数据，则会发生什么。那么 INNER JOIN 解决方案提供者 @Becuzz 就不起作用了，因为它只会返回每天都有两个条目的行。 您可以使用多个 JOIN 使用 FULL JOIN，即使每天只有一个条目，也会返回数据：
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];
请参见
带演示的SQL Fiddle。多重连接的另一个问题是如果您有超过2个值，您需要为每个值添加一个附加连接。
如果您只有有限数量的值，则建议使用聚合函数和CASE表达式来获取结果：
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];
请查看带演示的SQL Fiddle。或者像@M.Ali一样使用PIVOT/UNPIVOT。这些方法的问题在于如果你有未知的值——意味着不仅仅是Apple和Orange，那么你只能使用动态SQL来获取结果。动态SQL将创建一个需要由引擎执行的SQL字符串：
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;
请查看带演示的SQL Fiddle
所有版本均给出以下结果：
| 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 |
