CODEWITHSUNDEEP
sqlsql-server-2008exec
techblog
techblog

Reputation: 529

How to display column name as data in another column

I have a query like this.

EXEC('SELECT FirstName, LastName,' + @Subject + ' FROM Student')

I have to display one more column which is first 2 letters of the dynamically selected column name "Subject". I have tried using SUBSTRING function as below but it fetches first 2 letter of the data. Instead I want to fetch first 2 letters of the column name. Please let me know what I am doing wrong here.

EXEC('SELECT FirstName, LastName,' + @Subject + ', SUBSTRING('+@Subject+',1,2)  FROM Student')

Upvotes: 0

Views: 133

Answers (2)

techblog
techblog

Reputation: 529

DECLARE @SUBJECT_SPLIT VARCHAR(10)
SET @SUBJECT_SPLIT = SUBSTRING(@subject,1,2)
EXEC('SELECT FirstName, LastName,' + @Subject + ', ''' + @SUBJECT_SPLIT + '''
FROM Student')

I gives correct values with 3 inverted commas around +@SUBJECT_SPLIT+. I have no idea why it worked with 3 inverted commas, it will be of great help if someone can it.

Upvotes: 0

A.J
A.J

Reputation: 382

You can move the substring outside:

Assuming the column will be physically present in the table:

DECLARE @SUBJECT_SPLIT VARCHAR(10)
SET @SUBJECT_SPLIT = SUBSTRING(@subject,1,2)
EXEC('SELECT FirstName, LastName,' + @Subject + ',' + @SUBJECT_SPLIT + ' FROM Student')

Assuming the column will not be present physcially:

DECLARE @SUBJECT_SPLIT VARCHAR(10)
SET @SUBJECT_SPLIT = SUBSTRING(@subject,1,2)
EXEC('SELECT FirstName, LastName,' + @Subject + ', '' AS ' + @SUBJECT_SPLIT + ' FROM Student')

Upvotes: 3

Related Questions