在T-SQL中使用Pivot [英] Using Pivot in T-SQL

问题描述

嘿所有，



我正在尝试在SQL Server数据库中的表上执行一个数据透视，但是我遇到了一个无用的错误消息。我的查询如下：

Hey all,

I'm trying to carry out a pivot on a table in a SQL Server Database but am hitting an unhelpful error message. My query is as follows:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ', ','') + QUOTENAME(EnquiryPeriod)
FROM (SELECT DISTINCT EnquiryPeriod FROM Enquiries) AS EnqPeriod

SET @DynamicPivotQuery =
  N'SELECT Children.ChildID, ' + @ColumnName + '
    FROM (Enquiries
    INNER JOIN CarerChildren ON CarerChildren.CarerID = Enquiries.CarerID)
    INNER JOIN Children ON CarerChildren.ChildID = Children.ChildID
    WHERE (((CarerChildren.DateMovedOn) Is Null))
    GROUP BY Children.ChildID, [Firstname] + [Surname], Children.DateOfBirth
    ORDER BY [Firstname] + [Surname], Enquiries.EnquiryPeriod
    PIVOT(Max(EnquiryDate)
          FOR EnquiryPeriod IN (' + @ColumnName + ')) AS PVTTable'

EXEC (@DynamicPivotQuery)

它给出错误单词'PIVOT'附近的语法不正确。这可能是一个简单的错误，只是想知道是否有人能发现我做错了什么？

It gives the error "Incorrect syntax near the word 'PIVOT'. It's probably a simple error, just wondering if anyone can spot what I'm doing wrong?

推荐答案

我没有使用数据透视工具，所以它只是一个猜测。不要介意我错了。

我认为你需要遵循以下语法

I havn't worked using pivot so its just a guess. Don't mind if I'm mistaken.
I think you need to follow this syntax as below

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

所以我认为你需要在调整之后下订单？



参考：使用PIVOT和UNPIVOT-TechNet-Microsoft [ ^ ]

您需要修改用于源的子查询，以便它具有将用于PIVOT的所有列，像这样：

You need to modify the sub-query that you use for your source so that it has all columns that will be used for your PIVOT, like this:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ', ','') + QUOTENAME(EnquiryPeriod)
FROM (SELECT DISTINCT EnquiryPeriod FROM Enquiries) AS EnqPeriod
 
SET @DynamicPivotQuery =
  N'SELECT ChildID, [childName], ' + @ColumnName + '
    FROM (SELECT Children.ChildID, [Firstname] + [Surname] as [childName],
    Enquiries.EnquiryPeriod, EnquiryDate
    FROM Enquiries
    INNER JOIN CarerChildren ON CarerChildren.CarerID = Enquiries.CarerID)
    INNER JOIN Children ON CarerChildren.ChildID = Children.ChildID
    WHERE CarerChildren.DateMovedOn Is Null) AS pvtSource
    PIVOT(Max(EnquiryDate)
          FOR EnquiryPeriod IN (' + @ColumnName + ')) AS PVTTable'
 
EXEC (@DynamicPivotQuery)

两件事：

- > ISNULL（@ColumnName +'，'，''）更好：COALESCE（@ColumnName +'，'，''）

- >是QUOTENAME（EnquiryPeriod）还用逗号分隔？不，但如果明显导致超过1行.....你有问题。

在我的文章 [ ^ ]你看到一个处理该方法的方法

Two things:
-> ISNULL(@ColumnName + ', ','') better: COALESCE(@ColumnName +', ', '')
-> is QUOTENAME(EnquiryPeriod) separating also with a comma? NO but if the distinct results in more than 1 row.....you have a problem.
In my article [^]you see a method to deal with that

这篇关于在T-SQL中使用Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！