Converting Rows into Columns with Custom "as" statement

Question

I have the following query to convert rows into columns. I need to tag the columns with an _name at the end of the column name.

The following part of the below query:

as QUOTENAME(FieldName) + '_name'

gives the following error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

Query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(FieldName) as QUOTENAME(FieldName) + '_name'
                    from [LookUp].[CustomField]
                    where FieldTable = 'Clientbackground'
                    group by FieldName, CustomFieldID
                    order by CustomFieldID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select isRequired, FieldName
                from [LookUp].[CustomField]
                where FieldTable = ''Clientbackground''
            ) x
            pivot 
            (
                max(isRequired)
                for FieldName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Does anyone know how I can correct this?

CREATE TABLE [LookUp].[CustomField](
    [CustomFieldID] [smallint] IDENTITY(1,1) NOT NULL,
    [FieldTable] [nvarchar](100) NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [Label] [nvarchar](500) NOT NULL,
    [Description] [nvarchar](500) NOT NULL,
    [IsVisible] [int] NOT NULL,
    [IsRequired] [int] NOT NULL,
    [IsAutoAlert] [int] NOT NULL,
 CONSTRAINT [PK_CustomField] PRIMARY KEY CLUSTERED 
(
    [CustomFieldID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (148, N'ClientBackGround', N'FieldName1', N'update label', N'update description', 1, 0, 0)
GO
INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (149, N'ClientBackGround', N'FieldName2', N'update label', N'update description', 1, 0, 0)
GO
INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (150, N'ClientBackGround', N'FieldName3', N'update label', N'update description', 1, 0, 0)
GO
INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (151, N'ClientBackGround', N'FieldName4', N'update label', N'update description', 1, 0, 0)
GO
INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (152, N'ClientBackGround', N'FieldName5', N'update label', N'update description', 1, 0, 0)
GO
INSERT [LookUp].[CustomField] ([CustomFieldID], [FieldTable], [FieldName], [Label], [Description], [IsVisible], [IsRequired], [IsAutoAlert]) VALUES (153, N'ClientBackGround', N'FieldName6', N'update label', N'update description', 1, 0, 0)

Answer 1

An Alias can't be an expression, it has to be an explicit value. You will have to split your pivoting values in 2: one for the pivot and another one for the SELECT list.

DECLARE 
    @colsPivot AS NVARCHAR(MAX),
    @colsSelect AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT ',' + QUOTENAME(FieldName)
                    from [LookUp].[CustomField]
                    where FieldTable = 'Clientbackground'
                    group by FieldName, CustomFieldID
                    order by CustomFieldID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsSelect = STUFF((SELECT ',' + QUOTENAME(FieldName)  + ' as ' + QUOTENAME(FieldName + '_name')
                    from [LookUp].[CustomField]
                    where FieldTable = 'Clientbackground'
                    group by FieldName, CustomFieldID
                    order by CustomFieldID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @colsSelect + N' from 
             (
                select isRequired, FieldName
                from [LookUp].[CustomField]
                where FieldTable = ''Clientbackground''
            ) x
            pivot 
            (
                max(isRequired)
                for FieldName in (' + @colsPivot + N')
            ) p '

exec sp_executesql @query;

Answer 2

You need two variables for columns - one for columns in select, one for columns in the pivot clause:

DECLARE @cols_p AS NVARCHAR(MAX),
      @cols_s AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols_s = STUFF((SELECT ',' + QUOTENAME(FieldName) +  ' as ' + QUOTENAME(FieldName + '_name')  
                    from [LookUp].[CustomField]
                    where FieldTable = 'Clientbackground'
                    group by FieldName, CustomFieldID
                    order by CustomFieldID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols_p = STUFF((SELECT ',' + QUOTENAME(FieldName) 
                    from [LookUp].[CustomField]
                    where FieldTable = 'Clientbackground'
                    group by FieldName, CustomFieldID
                    order by CustomFieldID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols_s + N' from 
             (
                select isRequired, FieldName
                from [LookUp].[CustomField]
                where FieldTable = ''Clientbackground''
            ) x
            pivot 
            (
                max(isRequired)
                for FieldName in (' + @cols_p + N')
            ) p '

exec sp_executesql @query;