如何转动或忽略 | 如何转动或忽略

本文介绍了如何转动或忽略的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我只是不了解旋转，我需要一个简单查询的帮助。我的查询给了我作为研究员的结果：

Hi,
I just don't understanding pivoting and i need help on a simple query. My query gives me the results as fellows:

SELECT 
    'LuFt' AS [Source],
	SUM([SalesAmount1]) AS [Sales old],
	SUM([SalesAmount2]) AS [Sales new]
FROM [dbo].[tblSales]
 WHERE ([Source] IN ('LuFt')) AND [Planung] = 'kosten'

UNION

SELECT 
    'TGL' AS [Source],
	SUM([SalesAmount1]) AS [Sales old],
	SUM([SalesAmount2]) AS [Sales new]
FROM [dbo].[tblSales]
 WHERE ([Source] IN ('TGL')) AND [Planung] = 'kosten'

 UNION

SELECT 
    'BHW' AS [Source],
	SUM([SalesAmount1]) AS [Sales old],
	SUM([SalesAmount2]) AS [Sales new]
FROM [dbo].[tblSales]
 WHERE ([Source] IN ('BHW')) AND [Planung] = 'kosten'

  UNION

SELECT 
    'WM ' AS [Source],
	SUM([SalesAmount1]) AS [Sales old],
	SUM([SalesAmount2]) AS [Sales new]
FROM [dbo].[tblSales]
 WHERE ([Source] IN ('WM')) AND [Planung] = 'kosten'

结果：

Results:

[Source]      [Sales old]      [Sales new]
 Luft           10000            20000
 TGL            500              800
 BHW            1500             500
 WM             700              400

我的期望使用支点：

My Expectation using pivot:

[]          [Luft]   [TGL]  [BHW]  [WM]
Sales old    10000    500    1500   700
Sales new    20000    800    500    400

推荐答案

SELECT [DataDescription], [Luft], [TGL], [BHW], [WM]
FROM (
    SELECT 'Sales Old' AS DataDescription, [Source], [SalesAmount1] As MySales
    FROM [dbo].[tblSales]
    WHERE [Planung] = 'kosten'
    UNION ALL
    SELECT 'Sales New' AS DataDescription, [Source], [SalesAmount2] As MySales
    FROM [dbo].[tblSales]
    WHERE [Planung] = 'kosten'
) AS DT
PIVOT(SUM([MySales]) FOR [Source] IN([Luft], [TGL], [BHW], [WM])) AS PVT

这篇关于如何转动或忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！