列数未知的SQL数据透视-不是有效的标识符，仅显示最小值

Question

我在MS SQL server 2014中遇到了一个以未知列数为中心的动态查询问题。我的查询基于文章SQLServer2005PivotonUnknownNumberofColumns和其他类似的文章。然而，我有两个问题无法破解。

在执行动态SQL时，我得到了以下错误：

The name 'SELECT Code, LastName, FirstName, [03-30-2021],[06-30-2021],[08-00-2021],[10-30-2021],[12-30-2021],[17-30-2021],[18-30-2021],[19-30-2021],[20-30-2021],[21-30-2021],[22-30-2021],[24-30-2021],[25-30-2021],[26-30-2021],[29-30-2021] FROM (
SELECT i.Code, aa.LastName, aa.FirstName, FORMAT(StartDate, 'dd-mm-yyyy') AS StartDate, FORMAT(s.SignOut-s.SignIn, 'hh:mm') AS AttendanceTime
FROM ActualSession AS a INNER JOIN ActualAttendee aa ON( a.id = aa.ActualSessionId)
INNER JOIN Attendee att ON (att.Id = aa.AttendeeId) 
LEFT JOIN SignIn s ON (a.Id = s.ActualSessionId) LEFT JOIN Identification i ON (i.AttendeeId = aa.AttendeeId 
AND i.Id' is not a valid identifier.

但是，如果我将查询复制并粘贴到SQLServerManagementStudio中的一个单独窗口中，它将运行。。。我看不出有什么问题，只是错误消息只返回了查询的前xxx个字符。。。任何关于我做错了什么的建议都将不胜感激。是联接，还是我选择列的方式？

但是。。。这就引出了第二个问题。当我单独运行复制的查询文本时，它可以工作，但它为所有用户显示相同的值(以小时/分钟为单位的时间(，而不是特定于用户的值(尽管没有登录的用户正确地显示为NULL(。pivot似乎需要一个min/max/other聚合，但在它应该将其保留给每个用户的地方(根据我所看到的，通过一些SQL伏都教(，它不是。。。列名似乎也是以美国日期格式出现的，尽管我已经将格式指定为澳大利亚(dd-mm-yyyy(。如果有人知道如何纠正这些问题，我们也将不胜感激。

Code    LastName    FirstName   03-30-2021  06-30-2021  08-00-2021  10-30-2021
abc123  Simpson     Homer       01:07       01:15       NULL        01:01
abc456  Griffen     Peter       01:07       01:15       NULL        01:01
abc789  Flintsone   Fred        01:07       01:15       NULL        01:01
xyz123  Heffernan   Doug        01:07       01:15       NULL        01:01
xyz456  Gergich     Jerry       NULL        NULL        NULL        NULL
xyz789  Kramden     Ralph       01:07       01:15       NULL        01:01

我正在运行的完整查询是：

@Query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT DISTINCT '],[' + FORMAT(StartDate, 'dd-mm-yyyy') FROM ActualSession
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') + ']'
SET @Query = 'SELECT Code, LastName, FirstName, ' + @cols + ' FROM (
SELECT i.Code, aa.LastName, aa.FirstName, FORMAT(StartDate, ''dd-mm-yyyy'') AS StartDate, FORMAT(s.SignOut-s.SignIn, ''hh:mm'') AS AttendanceTime
FROM ActualSession AS a INNER JOIN ActualAttendee aa ON( a.id = aa.ActualSessionId)
INNER JOIN Attendee att ON (att.Id = aa.AttendeeId) 
LEFT JOIN SignIn s ON (a.Id = s.ActualSessionId) LEFT JOIN Identification i ON (i.AttendeeId = aa.AttendeeId 
AND i.IdentificationTypeId = (SELECT Id FROM IdentificationType WHERE [Name] = ''Student Code''))
) x PIVOT ( max(AttendanceTime) 
FOR StartDate in (' + @cols + ') ) p '
PRINT @Query  --for debugging
execute @Query

相关表格定义为：

CREATE TABLE [dbo].[ActualSession](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[EventId] [bigint] NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
[EventSessionId] [bigint] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Active] [bit] NULL,
[SignInRequired] [bit] NULL,
[SignOutRequired] [bit] NULL,
[SignInAllowed] [bit] NULL,
[SignOutAllowed] [bit] NULL,
[EarlySignInAllowed] [bit] NULL,
[EarlySignOutAllowed] [bit] NULL,
[LateSignInAllowed] [bit] NULL,
[LateSignOutAllowed] [bit] NULL,
[ExpiredIdAllowed] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Attendee](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[PreferredName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SignIn](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[EventId] [bigint] NOT NULL,
[ActualSessionId] [bigint] NOT NULL,
[AttendeeId] [bigint] NOT NULL,
[SignIn] [datetime] NOT NULL,
[SignOut] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Identification](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[AttendeeId] [bigint] NOT NULL,
[IdentificationTypeId] [bigint] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[ExpiryDate] [date] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[IdentificationType](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[RevHex] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ActualAttendee](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[EventId] [bigint] NOT NULL,
[ActualSessionId] [bigint] NOT NULL,
[AttendeeId] [bigint] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[PreferredName] [nvarchar](50) NULL,
[GroupId] [bigint] NULL,
[GroupName] [nvarchar](50) NULL,
[GroupTypeId] [bigint] NULL,
[GroupTypeName] [nvarchar](50) NULL,
[GroupTypeAD] [bit] NULL,
[GroupTypeADName] [nvarchar](200) NULL,
[GroupTypeDB] [bit] NULL,
[GroupTypeDBQuery] [nvarchar](1000) NULL,
[GroupTypeManual] [bit] NULL
) ON [PRIMARY]
GO

Answer 1

这里有两个问题：

• 您的主要问题是：当您调用execute时，需要将@Query括在括号()中。不过，我建议您使用sp_executesql，因为这使您能够在必要时传递参数
• 第二个问题：不要自己引用列名，使用QUOTENAME

DECLARE @Query AS nvarchar(MAX), @cols nvarchar(MAX);
SELECT @cols = STUFF((
SELECT DISTINCT ',' + QUOTENAME(FORMAT(StartDate, 'dd-mm-yyyy'))
FROM ActualSession
FOR XML PATH(''), TYPE
).value('text()[1]', 'NVARCHAR(MAX)')
, 1, LEN(','), '');
SET @Query = '
SELECT
Code,
LastName,
FirstName,
' + @cols + '
FROM (
SELECT
i.Code,
aa.LastName,
aa.FirstName,
FORMAT(StartDate, ''dd-mm-yyyy'') AS StartDate,
FORMAT(s.SignOut-s.SignIn, ''hh:mm'') AS AttendanceTime
FROM ActualSession AS a
INNER JOIN ActualAttendee aa ON (a.id = aa.ActualSessionId)
INNER JOIN Attendee att ON (att.Id = aa.AttendeeId) 
LEFT JOIN SignIn s ON (a.Id = s.ActualSessionId)
LEFT JOIN Identification i ON (i.AttendeeId = aa.AttendeeId 
AND i.IdentificationTypeId = (SELECT Id FROM IdentificationType WHERE [Name] = ''Student Code''))
) x
PIVOT ( max(AttendanceTime) 
FOR StartDate in (
' + @cols + '
) ) p;
';
PRINT @Query  --for debugging
EXEC sp_executesql @Query;

Answer 2

我相信您收到的错误与使用execute @Query而不是execute (@Query)有关。查看EXECUTE上的Microsoft文档，执行字符串变量需要打开和关闭parens:

Execute a character string  
{ EXEC | EXECUTE }   
( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )  
[ AS { LOGIN | USER } = ' name ' ]  
[;] 

一旦我添加了parens，我就可以得到一个我认为与您的语句等效的语句，以便使用我创建的一些示例数据按预期执行。

至于你的日期格式，我认为你的日期格式化字符串有问题。我相信您当前的日期格式字符串dd-mm-yyyy应该是dd-MM-yyyy。根据自定义日期和时间格式字符串Microsoft文档(由FORMAT上的Microsoft文档引用(：

• "mm"；一分钟，从00到59
• "MM"；月份，从01到12