What is the T-SQL syntax to exclude a duplicate column in the output when joining 2 tables?

Question

I am using SQL Server 2014 and I have the following T-SQL query which joins 2 tables:

SELECT a.*, b.* FROM TEMP a

INNER JOIN Extras b ON b.ResaID = a.ResaID

I would like to pull ALL the columns from TEMP and all the columns from "Extras" with the exception of the ResaID column as it is already included in a.* in the above query. Basically, I want to pull a.* + b.* (excluding b.ResaID).

I know I can write the query in the form:

Select a.*, b.column2, b.column3,...

but since b.* has got around 40 columns, is there a way to write the query in a more simplified way to exclude b.ResaID, rather than specify each of the columns in the "Extras" table?

this syntax is called "column alias" and "dont use select `*`" — Ivan Starostin, Mar 08 '16 at 07:47
I don't believe this is possible without resorting to ideas suggested in answers to [this question](http://stackoverflow.com/q/729197/685760). I think you just need to be explicit in which columns you require. — Mr Moose, Mar 08 '16 at 07:47
@MrMoose Thanks for pointing to that question. I missed it from my search. I will now have a look at the answers! — user3115933, Mar 08 '16 at 07:49
[Bad Habits to Kick: using SELECT *](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list) — Damien_The_Unbeliever, Mar 08 '16 at 07:50
you could also create a view,if you don't want to list all 40 columns in select — TheGameiswar, Mar 08 '16 at 08:02
You can solve this using dynamic sql http://stackoverflow.com/questions/35861714/what-is-the-t-sql-syntax-to-exclude-a-duplicate-column-in-the-output-when-joinin/35863902#35863902 — Abdul Rasheed, Apr 29 '16 at 06:04

score 1 · Answer 1 · answered Mar 08 '16 at 07:48

1

Unfortunately, there is no such syntax. You could either use asterisks (*) and just ignore the duplicated column in your code, or explicitly list the columns you need.

answered Mar 08 '16 at 07:48

Mureinik

297,002
52
306
350

score 0 · Answer 2 · answered Mar 08 '16 at 09:06

You should create a view and select the columns you need from that view. Here is a script that will generate that view for you:

DECLARE @table1 nvarchar(20) = 'temp'
DECLARE @table1key nvarchar(20) = 'ResaID'

DECLARE @table2 nvarchar(20) = 'Extras'
DECLARE @table2key nvarchar(20) = 'ResaID'

DECLARE @viewname varchar(20) = 'v_myview'

DECLARE @sql varchar(max) = ''
SELECT @sql += '], a.[' + column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1

SELECT @sql += '], b.[' + column_name
FROM
(
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table2
  EXCEPT
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table1
) x

SELECT 
  @sql = 'CREATE view ' +@viewname+ ' as SELECT ' 
  + STUFF(@sql, 1, 3, '') + '] FROM [' 
  +@table1+ '] a JOIN ['+ @table2 
  +'] b ON ' + 'a.' + @table1key + '=b.' + @table2key


EXEC(@sql)

Abdul Rasheed · Answer 3 · 2017-03-28T04:22:08.703

You can simply solve this using a dynamic sql query.

DECLARE @V_SQL   AS NVARCHAR(2000)='' --variable to store dynamic query
        ,@V_TAB1 AS NVARCHAR(200)='TEMP' --First Table
        ,@V_TAB2 AS NVARCHAR(200)='Extras' --Second Table
        ,@V_CONDITION AS NVARCHAR(2000)='A.ResaID = B.ResaID' --Conditions
SELECT  @V_SQL  =   STUFF(
    (   SELECT ', '+TCOL_NAME
        FROM
        (   SELECT 'A.'+S.NAME AS TCOL_NAME 
            FROM SYSCOLUMNS AS S 
            WHERE OBJECT_NAME(ID) = @V_TAB1
            UNION ALL
            SELECT 'B.'+S.NAME 
            FROM SYSCOLUMNS AS S 
            WHERE   OBJECT_NAME(ID) = @V_TAB2
                AND S.NAME NOT IN (SELECT S.NAME 
                                   FROM SYSCOLUMNS AS S 
                                   WHERE OBJECT_NAME(ID) = @V_TAB1)
        )   D
        FOR XML PATH('')
    ),1,2,'')
EXECUTE ('SELECT '+@V_SQL+' 
             FROM '+@V_TAB1+' AS A 
             INNER JOIN '+@V_TAB2+' AS B ON '+@V_CONDITION+' ')

What is the T-SQL syntax to exclude a duplicate column in the output when joining 2 tables?

3 Answers3