SQL pivot with unknown number of columns - not a valid identifier and showing minimum only

Question

I am having an issue with a dynamic query to pivot on an unknown number of columns in MS SQL server 2014. I've based my query on the article SQL Server 2005 Pivot on Unknown Number of Columns and other similar articles. However, I am having two issues that I can't decipher.

When executing the dynamic SQL, I get this error:

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.

However, if I copy and paste the query into a separate window in SQL Server Management Studio, it runs... I can't see what is wrong, except that the error message is only returning the first xxx characters of the query... Any suggestions as to what I have done wrong would be appreciated. Is it the joins, or the way I've selected columns?

BUT... that takes me to the second issue. When I do run the copied query text separately, it works but it is showing the same values (time in hours/minutes) for all users, rather than the user-specific values (though users who did not sign in are correctly coming up NULL). The pivot seems to need a min/max/other aggregate, but where it should be keeping it to each user (according to what I can see, by some SQL voodoo), it's not... The column names also appear to be coming up in US date format, even though I've specified the format as Australian (dd-mm-yyyy). If anyone knows how to correct these issues, that would also be appreciated.

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

The full query I am running is:

@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

Relevant Table definitions are:

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

Your query here is mixing implicit conversions between varchar and nvarchar, which you should probably resolve. Beyond that, your string will not be recognized as nvarchar(max) unless you force one of the strings to be an nvarchar(max) (or it comes in that way). Common workaround is to prefix your string with `CONCAT(CAST(N'' AS nvarchar(max)), @String1, @String2, etc....)`. Posting table definitions and sample data to the objects involved here along with your desired output will ensure you get a better, more accurate answer. — trenton-ftw, Jul 13 '21 at 06:04
@trenton-ftw Thanks. Table definitions added. It'll take me longer to get relevant data (and hence expected results), so see if this helps enough. Output data should be as shown, but with different times for each person (as they would have signed in and out at different times - and should be around the 3 hour mark for most (give or take 20 minutes), rather than all identical. I tried explicit casting to NVARCHAR(MAX) as recommended - no change (not that I think either of us was expecting it). — Dion, Jul 13 '21 at 06:24
If this is just relating to your `PRINT @Query` output, [Per Microsoft](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver15), `PRINT` will only ever output a max of 4,000 unicode characters. So it might be the case that you are only seeing the first 4,000 characters in your `PRINT` output, but in reality your entire string is being stored (once you have verified they are being stored in nvarchar(max)). As a work around, you can either print it in batches of 4,000 characters or return it as a result using `SELECT`. — trenton-ftw, Jul 13 '21 at 06:29
Looks like your question is still missing a schema definition for what appears to be a table named `ActualAttendee` — trenton-ftw, Jul 13 '21 at 06:51
@trenton-ftw - added ActualAttendee. `PRINT @Query` isn't the issue - that prints the whole statement. — Dion, Jul 13 '21 at 14:48
Have you actually validated that the base query with `AttendanceTime` is actually corect? Are the joins correct? We don't have any of your data so can't say. What are the actual column data types? — Charlieface, Jul 13 '21 at 15:05
@Dion I know its not an issue here, but I assumed that this was a simplified example. If there were enough dates being used in this pivot then it could result in a query where the PRINT statement would not be printing the entire query or even in the variable `@Query` not holding the entire query without ensuring it was storing it as nvarchar(max). — trenton-ftw, Jul 13 '21 at 15:11
Agreed with @Charlieface. Your PIVOT statement set up looks correct according to your desired output. If the data is still not coming out the way that you expect then you likely need to re-evaluate the base query (being your subquery aliased as `x`). Unless you provide us with sample data and desired output from that sample data I'm not sure we can tell you why it is/isn't coming out the way you expect. — trenton-ftw, Jul 13 '21 at 15:23

Charlieface · Answer 1 · 2021-07-13T15:25:19.393

You have two issues here:

Your main issue: when you call execute you need to enclose @Query in parenthesis (). I suggest, though, that you use sp_executesql, because this enables you to pass through parameters if necessary
Your second problem: don't try to quote the column names yourself, use 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;

thanks @Charlieface. QUOTENAME is certainly better code. As above, users are still producing identical times, rather than times specific to each user. — Dion, Jul 13 '21 at 14:57

score 0 · Answer 2 · answered Jul 13 '21 at 07:09

I believe the error you are receiving is related to the usage of execute @Query instead of execute (@Query). Looking at the Microsoft doc on EXECUTE, executing a string variable requires open and close parens:

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

Once I added the parens, I was able to get what I believe is an equivalent statement to yours to execute as expected with some sample data I created.

As for your date format, I believe your date format string has an issue. I believe your current date format string of dd-mm-yyyy is meant to be dd-MM-yyyy. Per the Custom date and time format strings Microsoft doc (referenced by Microsoft doc on FORMAT):

"mm" The minute, from 00 through 59.
"MM" The month, from 01 through 12.

Thanks @trenton-ftw. This fixes the minor issues. The statement now executes and now shows the correct dates (silly me - I should know better). However, it still shows identical times, rather than different/actual times for each user. — Dion, Jul 13 '21 at 14:51

SQL pivot with unknown number of columns - not a valid identifier and showing minimum only

2 Answers2