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

You can't do this with an `*`, unless you use some heavy dynamic SQL which is way harder than writing the new aliases by hand. — EzLo, Dec 27 '18 at 16:00
You may find some assistance here, though not a direct answer: https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server — Steve-o169, Dec 27 '18 at 16:18
Why is there not any command like: display full column name ? or display column name address .. or something like that? — Wolfish, Dec 27 '18 at 16:51

score 1 · Answer 1 · answered Dec 27 '18 at 15:59

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.

I have like 150 fields and I would like to do it automatically, not by hand. But if that is not possible that is strange because it is so straight forward logically. — Wolfish, Dec 27 '18 at 16:05

score 0 · Answer 2 · answered Dec 27 '18 at 16:12

0

You can use the query below to obtain the list of fields separated by commas and then copy and paste into your query:

SELECT T.Name as Table_Name, 
    'L.'+C.Name+' AS ''L.'+C.Name+''', ' as Column_Name,
    'LI.'+C.Name+' AS ''LI.'+C.Name+''', ' as Column_Name
FROM sysobjects T 
INNER JOIN syscolumns C
  ON T.ID = C.ID
WHERE T.Name LIKE '%Airlines%';

More tricks about Data Dictionary Usage under: Tips 4 DBs

answered Dec 27 '18 at 16:12

Angel M.

1,360
8
17

If my suggestion did help, please, consider to select as "the reply". Thanks – Angel M. Dec 28 '18 at 20:42

score 0 · Answer 3 · answered Dec 27 '18 at 16:45

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

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

3 Answers3