Listing Comma Separated Columns instead of ‘*’ for SELECT Statement in SQL Server

Written by
Listing Comma Separated Columns instead of ‘*’for SELECT Statement in SQL Server - Arul Mouzhi

Introduction

In any scenario, if we need to explicitly list out all the column names of a table in the SELECT statement instead of using ‘*’, we can use like below-

Sample SQL Script
IF(OBJECT_ID('TestEmp') IS NOT NULL)
DROP TABLE [TestEmp];
CREATE TABLE dbo.[TestEmp]
(
    TestEmp_Key INT IDENTITY(1,1) NOT NULL,
    EmpName VARCHAR(100) NOT NULL,
    Age INT NULL,
    [Address] VARCHAR(100) NULL,
    Inserted_dte DATETIME NOT NULL
 CONSTRAINT [PK_TestEmp] PRIMARY KEY CLUSTERED 
 (
    TestEmp_Key ASC
 )
);
GO
INSERT INTO dbo.TestEmp
VALUES ('Arul',24,'xxxyyy',GETDATE()),('Kiran',22,'zzzyyy',GETDATE());
GO
--Using '*'
SELECT * FROM [TestEmp];
GO
Sample Image
Listing Comma Separated Columns in Vertical manner for SELECT Statement

Listing Comma Separated Columns in Vertical manner for SELECT Statement

SQL Script
--Using Column Names with ',' instead of '*' (Vertical)
 SELECT CONCAT(NAME,',') AS COLUMN_NAMES FROM SYS.COLUMNS WHERE [OBJECT_ID]=OBJECT_ID('TestEmp');
 GO

Listing Comma Separated Columns in Horizontal manner for SELECT Statement

SQL Script
--Using Column Names with ',' instead of '*' (Horizontal)
 SELECT STRING_AGG(NAME,',') AS COLUMN_NAMES FROM SYS.COLUMNS WHERE [OBJECT_ID]=OBJECT_ID('TestEmp');
 GO

Conclusion

Thus, we saw how can we List out Comma Separated Columns of a table in both Horizontal and Vertical manner for SELECT Statement in SQL Server. This Scripts are unit tested in both Azure SQL DB and On-Premises.

Thanks for reading!


Article Categories:
SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares