Click here to Skip to main content
14,494,314 members
Rate this:
Please Sign up or sign in to vote.
i work on SQL server 2012
my problem is column alias on dynamic SQL query not working
so how to solve problem
issue on this line
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
CONVERT(VARCHAR,' + @columnname + 'Date ,111)AS ' + @columnnameDate + '

i expect to be as lifecycleDate
but it still display as lifecycle
How to solve this issue
see here sample
PartNumber	CompanyName	DocumentID	FlowStatus	TeamName	OnlineUrl	OfflineUrl	LifecycleDate
PartNumber	CompanyName	DocumentID	FlowStatus	TeamName	OnlineUrl	OfflineUrl	Lifecycle
BCM5338MIQMG	Broadcom Inc.	29858	Done	LifeCycle	N/A	3/25/2020

lifecycle on second line i need it lifecycleDate
but it display lifecycle

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)
Updated 26-Mar-20 1:24am
CHill60 26-Mar-20 5:09am
Can you be more specific (e.g. use a worked example) - When I run your code I am not getting a problem

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
Your header row adds Date to the column name, but not to the column value.
SET @header = 'SELECT ... As OfflineUrl, ''' + Replace(@columnname, ',', ''',''') + 'Date'' As ' + @columnname + 'Date
Maciej Los 26-Mar-20 16:12pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100