枢轴 - SQL - 来自子查询的值

问题描述

我有一个这样的简单查询..

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.

有一些示例是使用 COALESCEGoogle 搜索> 创建列列表.不过，我更喜欢使用 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 - 来自子查询的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！