将 Access TRANSFORM/PIVOT 查询转换为 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13953134/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Access TRANSFORM/PIVOT query to SQL Server
提问by Vipin Jain
TRANSFORM Avg(CASE WHEN [temp].[sumUnits] > 0
THEN [temp].[SumAvgRent] / [temp].[sumUnits]
ELSE 0
END) AS Expr1
SELECT [temp].[Description]
FROM [temp]
GROUP BY [temp].[Description]
PIVOT [temp].[Period];
Need to convert this query for sql server
需要将此查询转换为 sql server
I have read all other posts but unable to convert this into the same
我已阅读所有其他帖子，但无法将其转换为相同的
回答by Mahmoud Gamal
Here is the equivalent version using the PIVOTtable operator:
这是使用PIVOT表运算符的等效版本：
SELECT *
FROM
(
SELECT
CASE
WHEN sumUnits > 0
THEN SumAvgRent / sumUnits ELSE 0
END AS Expr1,
Description,
Period
FROM temp
) t
PIVOT
(
AVG(Expr1)
FOR Period IN(Period1, Period2, Period3)
) p;
SQL Fiddle Demo
SQL 小提琴演示
For instance, this will give you:
例如，这将为您提供：
| DESCRIPTION | PERIOD1 | PERIOD2 | PERIOD3 |
---------------------------------------------
| D1 | 10 | 0 | 20 |
| D2 | 100 | 1000 | 0 |
| D3 | 50 | 10 | 2 |
Note thatWhen using the MS SQL Server PIVOTtable operator, you have to enter the values for the pivoted column. However, IN MS Access, This was the work that TRANSFORMwith PIVOTdo, which is getting the values of the pivoted column dynamically. In this case you have to do this dynamically with the PIVOToperator, like so:
请注意，在使用 MS SQL ServerPIVOT表运算符时，您必须输入透视列的值。然而，在MS Access，这是该作品是TRANSFORM用PIVOT做的，这是动态获取枢转列的值。在这种情况下，您必须使用PIVOT运算符动态执行此操作，如下所示：
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT distinct
',' +
QUOTENAME(Period)
FROM temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = ' SELECT Description, ' + @cols + '
FROM
(
SELECT
CASE
WHEN sumUnits > 0
THEN SumAvgRent / sumUnits ELSE 0
END AS Expr1,
Description,
Period
FROM temp
) t
PIVOT
(
AVG(Expr1)
FOR Period IN( ' + @cols + ')
) p ';
Execute(@query);
Updated SQL Fiddle Demo
更新的 SQL Fiddle 演示
This should give you the same result:
这应该给你相同的结果：
| DESCRIPTION | PERIOD1 | PERIOD2 | PERIOD3 |
---------------------------------------------
| D1 | 10 | 0 | 20 |
| D2 | 100 | 1000 | 0 |
| D3 | 50 | 10 | 2 |
回答by Eduardo
TRANSFORM SUM(s2+s1)
SELECT PlanoContas.Conta AS Conta,
SPACE(LEN(PlanoContas.Conta)/2) + PlanoContas.Extenso AS Extenso,
PlanoContas.Tipo,SUM(s2+s1) AS [01/04/14]
FROM PlanoContas
INNER JOIN TEMP ON
PlanoContas.Conta=LEFT(Temp.Conta,LEN(PlanoContas.Conta))
WHERE LEN(PlanoContas.Conta)<=16 AND (s1<>0 OR s2<>0)
GROUP BY PlanoContas.Conta,Extenso,Tipo
ORDER BY PlanoContas.Conta PIVOT Filial
