CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
rickyProgrammer
rickyProgrammer

Reputation: 1167

Using Union all to SQL Queries to create Dynamic Column Names

Please se my code below

set @query = 'SELECT tenantcode, locationd, name, MONTH,' +
'Year1 as' + '[' + @Year1  + ']'  +
',Year2 as' + '[' + @Year2  + ']'  + 
',Year3 as' + '[' + @Year3  + ']'  +
',Year4 as' + '[' + @Year4  + ']'  +
',Year5 as' + '[' + @Year5 + ']'  +
'from #SalesPerYear' +
'UNION ALL' +
'SELECT  tenantcode, locationd, name, total , t1, t2,t3,t4,t5 FROM #TotalSales ' 


EXECUTE (@query)

That is part of my codes in the desire in achieving a final output in SQL WHERE COLUMN NAMES should be dynamic (in this case, the changing YEAR NAME such as 2011, 2012,2013 etc)

When execute my stored procedure, there is an error like this

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

When I remove the Union all, both queries worked individually. What could be the problem here.

Upvotes: 0

Views: 750

Answers (1)

Jamiec
Jamiec

Reputation: 136094

You're missing a space between from #SalesPerYear and UNION ALL and also the following SELECT.

....
'from #SalesPerYear '
                   ^---here
'UNION ALL' +
' SELECT  tenantcode, locationd, name, total , t1, t2,t3,t4,t5 FROM #TotalSales ' 
 ^--- and here

Thats the easiest way to demonstrate it, you can of course just put a space either side of UNION ALL

' UNION ALL ' +

Upvotes: 2

Related Questions