Alias To column not working when run dynamic query?

Transact-SQL
1

I work on SQL server 2012

my problem is column alias on dynamic SQL query not
working .

so how to solve problem

suppose column name is lifecycle

this meaning @columndate will be lifecycleDate

on print show that

but after that when use @columndate on query .

alias not working and still print column name

issue on this line

CONVERT(VARCHAR,' + @columnname + 'Date ,111)AS ' + @columnnameDate + '

I expect to be as lifecycleDate

but it still display as lifecycle

How to solve this issue

What I have tried:

DECLARE @columnname NVARCHAR(100)
DECLARE @columnnameDate NVARCHAR(100)
SET @columnname = (SELECT TOP 1 ColumnName FROM pcn.Teams t inner join #TempPC temp on t.TeamID= temp.TeamId)
SET @columnnameDate =@columnname + 'Date'
PRINT @columnname
PRINT @columnnameDate




DECLARE @header nvarchar(max)
SET @header='SELECT ''PartNumber'' as PartNumber,''CompanyName'' as CompanyName,''DocumentID'' AS DocumentID,''FlowStatus'' AS FlowStatus,''TeamName'' as TeamName,''OnlineUrl'' as OnlineUrl,''OfflineUrl'' as OfflineUrl,''' + replace(@columnname,',',''',''') + ''' AS ' +@columnname + 'Date
'


DECLARE @query nvarchar(max)
SET @query='
SELECT P.PartNumber,c.CompanyName,CAST(dpt.documentID AS NVARCHAR(20)) AS DocumentID,fs.FlowStatus,temp.TeamName,temp.OnlineUrl,temp.OfflineUrl ,CONVERT(VARCHAR,' + @columnname + 'Date ,111)AS ' + @columnnameDate + '
FROM pcn.DocumentPartTeams dpt with(nolock)
inner join #TempPC temp on dpt.documentID = temp.documentID
inner join parts.Nop_Part p with(nolock) on p.PartID=dpt.PartID
inner join Z2DataCompanyManagement.CompanyManagers.Company c with(nolock) on c.CompanyID=p.CompanyID
INNER JOIN pcn.documentteams dt with(nolock) on dpt.DocumentID=dt.DocumentID AND dt.teamid=temp.TeamId

inner join [Parts].[FlowStatus] FS with(nolock) on dpt.' + @columnname + '=fs.FlowStatusID
'
EXEC (@header + 'UNION ALL' + @query)

PartNumber|CompanyName|DocumentID|FlowStatus|TeamName|OnlineUrl|OfflineUrl|LifecycleDate

PartNumber|CompanyName|DocumentID|FlowStatus|TeamName|OnlineUrl|OfflineUrl|Lifecycle
BCM5338MIQMG|Broadcom Inc.|29858|Done|LifeCycle|N/A|https://source.z2data.com/2017/9/24/8/11/36/125/70073643/broadcom_eol002010_03232016_eol.pdf|3/25/2020

lifecycle on second line must be lifecycledate
but it display as lifecycle

2

Please provide some sample data for all of the tables in the query

At least show us what is in #TempPC

3

This isn't doing what you expect:

CONVERT(VARCHAR,' + @columnname + 'Date ,111)AS ' + @columnnameDate + '

You need

CONVERT(VARCHAR,' + @columnname ,111) AS [' + @columnname + 'Date]'

and it's good to put square brackets around the column names as well

2 Likes