作为列返回行值的SQL查询- Server 2008
作为列返回行值的SQL查询- Server 2008
提问于 2015-03-16 18:44:09
在想要将行值转换为列的情况下，我很难找到如何返回查询结果。
简而言之，下面是Server 2008中我当前模式的一个示例：
下面是一个我希望查询结果看起来像什么的例子：
下面是要玩的SQLFiddle.com - http://sqlfiddle.com/#!6/6b394/1/0
关于模式的一些有用的注释：
- 这个表每天总是包含两行。
- 一排苹果，一排橘子
- 我正在尝试将每对两行合并成一行。
- 要做到这一点，我需要将行值转换为它们自己的列，但只有NumOffered、NumTaken、NumAbandoned、NumSpoiled的值--并不是这两行中的每一列都需要复制，正如您从示例中看到的那样。
如您所见，从所需的最终结果图像中可以看到，这两行被组合在一起，并且您可以看到每个值都有自己的列，并有一个相关的名称。
我已经看到了几个例子，说明这是如何可能的，但并不完全适用于我的目的。我见过使用分组和案例方法的例子。我已经看到了PIVOT的许多用法，甚至在SQL中看到了一些自定义函数的创建。我不知道哪种方法对我来说是最好的。我能了解一下这件事吗？
回答 4
Stack Overflow用户
回答已采纳
发布于 2015-03-16 19:05:38
有很多不同的方法可以得到结果。多个联接、不枢轴/枢轴或有聚合的情况。
它们都有优点和缺点，所以你需要决定什么最适合你的情况。
多重连接--现在你已经说过，每天你会有两排--苹果和橙色一排。当多次在表上加入时，您需要某种类型的列来连接。列显示为timestamp，但如果您有一天只得到一行，会发生什么情况。然后，内连接解决方案由@Becuzz不起作用提供，因为它每天只返回两个条目的行。LeYou可以使用一个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];见与Demo。多个联接的另一个问题是，如果有两个以上的值，那么每个值都需要一个额外的联接。
如果您的值数量有限，那么我建议使用聚合函数和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];见与Demo。甚至像@M.Ali一样使用@M.Ali。如果您有未知的值，那么这些值的问题是什么--不仅仅是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;请参阅与Demo
所有版本都给出了结果：
| 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 |Stack Overflow用户
发布于 2015-03-16 18:57:10
根据您的标准，将两个伴生行连接在一起并选择合适的字段似乎是最简单的答案。你可以走支点，联合和团体的路线，等等，但这似乎是过火了。
select apples.Timestamp
, apples.[NumOffered] as ApplesNumOffered
, apples.[NumTaken] as ApplesNumTaken
, apples.[NumAbandoned] as ApplesNumAbandoned
, apples.[NumSpoiled] as ApplesNumSpoiled
, oranges.[NumOffered] as OrangesNumOffered
, oranges.[NumTaken] as OrangesNumTaken
, oranges.[NumAbandoned] as OrangesNumAbandoned
, oranges.[NumSpoiled] as OrangesNumSpoiled
from myTable apples
inner join myTable oranges on oranges.Timestamp = apples.Timestamp
where apples.FruitType = 'Apple'
and oranges.FruitType = 'Orange'Stack Overflow用户
发布于 2015-03-16 19:02:55
查询
;WITH CTE AS
(SELECT [Timestamp]
,FruitType + EventType AS Cols
,Qty
from myTable t
UNPIVOT (Qty FOR EventType IN (NumOffered ,NumTaken
,NumAbandoned,NumSpoiled))up
)
SELECT * FROM CTE
PIVOT (SUM(Qty) FOR Cols IN (AppleNumOffered,AppleNumTaken
,AppleNumAbandoned,AppleNumSpoiled
,OrangeNumOffered, OrangeNumTaken
,OrangeNumAbandoned,OrangeNumSpoiled))p结果
╔═════════════════════════╦═════════════════╦═══════════════╦═══════════════════╦═════════════════╦══════════════════╦════════════════╦════════════════════╦══════════════════╗
║ Timestamp ║ AppleNumOffered ║ AppleNumTaken ║ AppleNumAbandoned ║ AppleNumSpoiled ║ OrangeNumOffered ║ OrangeNumTaken ║ OrangeNumAbandoned ║ OrangeNumSpoiled ║
╠═════════════════════════╬═════════════════╬═══════════════╬═══════════════════╬═════════════════╬══════════════════╬════════════════╬════════════════════╬══════════════════╣
║ 2015-01-01 00:00:00.000 ║ 55 ║ 12 ║ 0 ║ 0 ║ 12 ║ 5 ║ 0 ║ 1 ║
║ 2015-01-02 00:00:00.000 ║ 21 ║ 6 ║ 2 ║ 1 ║ 60 ║ 43 ║ 0 ║ 0 ║
║ 2015-01-03 00:00:00.000 ║ 49 ║ 17 ║ 2 ║ 1 ║ 109 ║ 87 ║ 12 ║ 1 ║
║ 2015-01-04 00:00:00.000 ║ 6 ║ 4 ║ 0 ║ 0 ║ 53 ║ 40 ║ 0 ║ 1 ║
║ 2015-01-05 00:00:00.000 ║ 32 ║ 14 ║ 1 ║ 0 ║ 41 ║ 21 ║ 5 ║ 0 ║
║ 2015-01-06 00:00:00.000 ║ 26 ║ 24 ║ 0 ║ 1 ║ 97 ║ 30 ║ 10 ║ 1 ║
║ 2015-01-07 00:00:00.000 ║ 17 ║ 9 ║ 2 ║ 0 ║ 37 ║ 27 ║ 0 ║ 4 ║
║ 2015-01-08 00:00:00.000 ║ 83 ║ 80 ║ 3 ║ 0 ║ 117 ║ 100 ║ 5 ║ 1 ║
╚═════════════════════════╩═════════════════╩═══════════════╩═══════════════════╩═════════════════╩══════════════════╩════════════════╩════════════════════╩══════════════════╝SQL小提琴
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29084593
复制相关文章
相似问题
腾讯云开发者
