透视 - SQL - 来自子查询的值

发布于 2024-08-21 18:08:21 字数 1157 浏览 8 评论 0原文

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

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

但是数据透视查询中的值是硬编码的..我想从子查询中获取这些值..

select DaysToManufacture FROM Production.Product GROUP BY DaysToManufacture;

但是数据透视不允许我从子查询中获取值，是除了编写动态生成的查询之外，还有什么方法可以做到这一点？

原文

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

A simple pivot gives me

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;

Gives me

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;

But pivot doesn't let me get values from subquery, Is there any way to do this other than writing a dynamically generated query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

简单 2024-08-28 18:08:21

不可以。这只能使用动态查询来完成。我也很想知道是否有办法。

使用 COALESCE 快速 Google 搜索找到了一些示例来创建列列表。不过，我更喜欢使用 STUFF 创建列列表。不过我确实找到了这篇关于使用的文章 CTE 和动态枢轴也可能有帮助

回复收藏 0 原文

萌逼全场 2024-08-28 18:08:21

要从子查询中获取值，我们可以使用动态查询。我做了一些研究并找到了解决方案。

DECLARE @query VARCHAR(4000)
DECLARE @days VARCHAR(2000)
SELECT  @days = STUFF(( SELECT DISTINCT
                        '],[' + ltrim(str(DaysToManufacture))
                        FROM    Product
                        ORDER BY '],[' + ltrim(str(DaysToManufacture))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT ''AverageCost'' AS Cost_Sorted_By_Production_Days,'   +
@days + 
'FROM  (SELECT DaysToManufacture, StandardCost FROM Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN (' + @days +  ')) AS PivotTable;'  

EXECUTE (@query)

感谢 .NET 技术

To get the values from subquery we can use dynamic query. I have done some research and found a solution.

DECLARE @query VARCHAR(4000)
DECLARE @days VARCHAR(2000)
SELECT  @days = STUFF(( SELECT DISTINCT
                        '],[' + ltrim(str(DaysToManufacture))
                        FROM    Product
                        ORDER BY '],[' + ltrim(str(DaysToManufacture))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @query =
'SELECT ''AverageCost'' AS Cost_Sorted_By_Production_Days,'   +
@days + 
'FROM  (SELECT DaysToManufacture, StandardCost FROM Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN (' + @days +  ')) AS PivotTable;'  

EXECUTE (@query)

Thanks to .NET Technologies

回复收藏 0 原文

~没有更多了~