How to display table name as prefix to column name in SQL result

Question

Two tables. Some overlapping fieldnames. Join query. Same column name comes up twice. I would like to add the table name as a prefix to the column name just to make it easier to read. I am using the wild card * and I would like to continue to use the wildcard in the solution.

The query could be something like this:

SELECT *
FROM Loan l JOIN
     LoanInstallment li
     ON l.LoanId = li.LoanId

And the filed names of the result could be something like this:

LoanID | Amount | Date | LoanID | Amount | Date |
… records

Where I would like it to be

l.LoanID | l.Amount | l.Date | li.LoanID | li.Amount | li.Date |
… records

This should be very easy to do because it is obvious that SQL has no problem with having field names the same, so there is an invisible table name along with every column.

How do I display the table name, as a prefix, to the column name?

Happy Solstice

Answer 1

The names that you want are not the default names, so you need to set them explicitly. In addition, these are not "standard", so they need to be escaped. The escape character for columns varies by database, but most databases support the standard double quotes.

So:

SELECT l.LoanID as "l.LoanID",
       l.Amount as "l.Amount",
       l.Date as "l.Date",
       li.LoanID as "li.LoanID",  -- totally unnecessary because it is the same as l.LoanId
       li.Amount as "li.Amount",
       li.Date as "li.Date"
FROM Loan l JOIN
     LoanInstallment li
     ON l.LoanId = li.LoanId;

SQL Server traditionally uses square braces ([]') rather than double quotes to escape identifiers but it also supports identifiers.

Answer 2

use the below query to get the column name in a single line.

DECLARE @allcolname NVARCHAR(MAX)='';

SELECT @allcolname = @allcolname + 'L.' + C.Name +' AS ''L.'+C.Name+''', '
FROM sysobjects T 
INNER JOIN syscolumns C ON T.ID = C.ID
WHERE T.Name ='Users'; 

SELECT @allcolname