枢轴-SQL-子查询中的值 [英] Pivot - SQL - values from SubQuery
问题描述
我有一个像这样的简单查询.
I have a simple query like this..
USE AdventureWorks;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
一个简单的枢轴给了我
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
给我
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
但是数据透视查询中的值是硬编码..我想从子查询中获取这些值.
But the values in pivot query are hardcode.. I want to get those values from a subquery..
select DaysToManufacture FROM Production.Product GROUP BY DaysToManufacture;
但是pivot不允许我从子查询中获取值,除了编写动态生成的查询之外,还有其他方法可以做到这一点吗?
But pivot doesn't let me get values from subquery, Is there any way to do this other than writing a dynamically generated query?
推荐答案
否.这只能使用动态查询来完成.我真的很想知道是否有办法.
No. This can only be done using a dynamic query. I would be really interested to find out as well if there is a way.
有些示例可以使用COALESCE
创建列,从而快速地 Google搜索列表.但是,我更喜欢使用STUFF
创建列列表.但是,我确实找到了有关使用 CTE和动态枢纽可能也有帮助
There are some examples which a quick Google search found using COALESCE
to create the column list. However I prefer to create the list of columns using STUFF
. However I did find this article about the use CTE's and dynamic pivots which may be of assitance as well
这篇关于枢轴-SQL-子查询中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!