SQL Column Header

Other SQL Server Topics
1

Hi Everyone,

I have a unique problem in hand, is it possible to display the column alias with a value from another table?

e.g. SELECT TABLE1.COL1 AS TABLE1.COL1 + TABLE2.COL1 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID

Any help with regards to this is much appreciated.

Thanks,
Arun

2

My guess is that you can only do that with Dynamic SQL

3

Thanks Kristen for the response. Would you happen to have an example for the dynamic SQL? I am able to concatenate the two column data as a new column but the business user would like to see the value from the 2nd table as the column header.

SELECT CONVERT(nvarchar(50), b.FeeIn1)+'-'+CONVERT(nvarchar(50), a.Label_FeeIn1) AS FeeIn1
FROM [dbo].[Price_Line] b INNER JOIN
Project a on a.ProjectBillingID=b.ProjectBillingID
GO

4

Won't it change, potentially, from row-to-row?

5

Not really in this case, because the Column Label_Fee in the project table has same value for each billing id.

e.g. billing id 1 has the value REPLACE, billing id 2 has the value HELLO.

What I would like or the business user would like to see is FeeIn column from Priceline table concatenated with the LabelFee column in the project table and they only want it on the column header.

6 
DECLARE @SQL NVarchar(4000)
SELECT @SQL = 'SELECT  CONVERT(nvarchar(50), b.FeeIn1)+''-''+CONVERT(nvarchar(50), a.Label_FeeIn1) AS '
    + QuoteName(Project.Label_FeeIn1)
    + '  FROM [dbo].[Price_Line] b INNER JOIN  Project a on a.ProjectBillingID=b.ProjectBillingID'
FROM dbo.Project 
WHERE ProjectBillingID = 1234
EXEC (@SQL)

don't recommend it though - all sorts of issues with security, SQL injection and query performance (caching of query plans) and so on.

7

Thanks Kristen you are a life saver :grinning:. This is supposed to be a one time task since the data volume is huge they wanted it to be scripted. I will make sure that this is not used frequently.

Thanks once again.

8

I don't think frequently matters - its just a question of whether the code is secure / suitable. If you are satisfied on those points no reason not to use dynamic SQL ... but I do recommend that you make sure on that point :slight_smile: