SQLTeam.com | Weblogs | Forums

Alias To column not working when run dynamic query?

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

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

At least show us what is in #TempPC

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

1 Like