How to exclude one column from result?

Question

I have a table with columns

a,b,c,d,e,f,g,...,z

Is it possible to write a simple query that will gives all columns except a without manualy specifing all other columns?

something that will be equivelant to:

Select b,c,d,e,f,g,h,...z
from TableG

Answer 1

To answer your question, you cannot do that directly, HOWEVER i found a solution for you.

The SELECT statement of SQL when using Physical table can only do SELECT * that will return all columns and SELECT Column1, Column2, Column3... for specific columns, there is no WHERE condition that will exclude 1 column name in the SELECT statement of SQL. How ever you can manipulate the table and the data the way you wanted it using temporary table

Solution:

1. Insert into temp table
2. Drop the column that you want to exclude from the temp table

3. Select all data from temp table.

   SELECT * INTO #TempTable
   FROM TableG
   
   ALTER TABLE #TempTable
   DROP COLUMN a
   
   SELECT * FROM #TempTable
   
   DROP TABLE #TempTable
   

I found the solution here: SQL exclude a column using SELECT * [except columnA] FROM tableA?

Answer 2

/************************************************************
Function To Split Strings
************************************************************/

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.SplitString') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.SplitString
END
GO

create function [dbo].[SplitString]
(
    @String varchar(max),
    @Delimiter char(1)
)
returns @SplittedValues table
(
    str_item varchar(100) primary key
)
as
begin
    declare @SplitLength int

    while len(@String) > 0
    begin 
        select @SplitLength = (case charindex(@Delimiter,@String) when 0 then
            len(@String) else charindex(@Delimiter,@String) -1 end)

        insert into @SplittedValues
        select substring(@String,1,@SplitLength) 

        select @String = (case (len(@String) - @SplitLength) when 0 then  ''
            else right(@String, len(@String) - @SplitLength - 1) end)
    end 
return  
end
GO


/************************************************************
Function To Get columns names excluding some of them
************************************************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.get_columns') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.get_columns
END
GO
CREATE FUNCTION dbo.get_columns 
(
   @table_name varchar(100),
   @excluded_column_names varchar(100),
   @delimter char(1)
)
RETURNS varchar(4000)
AS
BEGIN
    declare @cols varchar(4000)
    select @cols = COALESCE(@cols+',' ,'') + name
    from sys.columns 
    where object_id=object_id(@table_name) 
        and name not in (select str_item from dbo.SplitString(@excluded_column_names,@delimter))
    return @cols
END
GO


/************************************************************
Function To Get columns names excluding some of them
************************************************************/
declare @qry nvarchar(max)
set @qry = ' select ' + dbo.get_columns('TableName','Exclude_col_1,Exclude_col_2,Exclude_col_3',',') 
    + ' from TableName'
    + ' where condition'
EXECUTE sp_executesql @qry
GO