SQL Puzzle | How to SELECT * except the specified columns in SQL

In this puzzle you have to select all columns except the specified columns in SQL. In this specific case we need all the columns BUT NAME column from the table named FindRecordsWithALength. Please check the sample input and the expected output.

Sample Input

Expected Output

Use below script to create table and insert sample data into it.

--

CREATE TABLE [dbo].[FindRecordsWithALength](
	[Id] [int] NULL,
	[Name] [varchar](10) NULL,
	[Length] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (1, N'A', 5)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (2, N'B', 3)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (3, N'C', 4)
GO
INSERT [dbo].[FindRecordsWithALength] ([Id], [Name], [Length]) VALUES (4, N'D', 5)
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

SELECT * INTO #temp from FindRecordsWithALength

DECLARE @columns VARCHAR(MAX) = ''
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @ExceptColumns VARCHAR(MAX) = 'Name,p'

SELECT @columns = ISNULL(@columns + ',','') + QUOTENAME(a.NAME) 
FROM tempdb.sys.columns a 
INNER JOIN tempdb.sys.objects s ON a.object_id = s.object_id
WHERE s.NAME like  '#temp%' AND s.Type = 'U'
AND NOT EXISTS ( SELECT value from 
(
	SELECT * FROM string_split(@ExceptColumns,',')
)u WHERE a.name = u.value
)

SET @SQL = 'SELECT ' + SUBSTRING(@columns,2,LEN(@columns))  + ' FROM #temp '

EXEC(@SQL)

--

Output-1

--                            


Id          Length
----------- -----------
1           5
2           3
3           4
4           5

(4 rows affected)



--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com