VALUES keyword in SQL Server

During my experience of working with SQL, i found that VALUES keyword can be used in different context. I do not found a blog which has all the stuff aggregated at one place, hence decided to put them all in one place.

 

1. Used in INSERT statement

This is the most basic used of VALUES keyword.we make the used of this to insert the values as below

 

INSERT INTO dbo.Students(StudentName,StudentGender,StudentAge) VALUES('Shadab','Male',28)

 

2. Used to create dataset without physically creating table

There can be some instance where we do not need to create a new physical or temporary table and still need to create the dataset, like in case of certain scenario we need to do union or unionall with a small set of data

 

SELECT * FROM (VALUES (5000,21),(6500,23),(7000,30)) AS t(Salary,Age)

 

3. Can be used in combination with Cross Apply and Outer Apply

To understand this better, there is a need to take into consideration a scenario, so we can follow along. Let us consider the below table structure

 

CREATE TABLE StudentSubject(StudentId int , Subject1 varchar(30),Subject2 varchar(30))   INSERT INTO StudentSubject VALUES (1,'English','Chemistry') INSERT INTO StudentSubject VALUES (1,'Computer','Maths')

 

Now, I want the output for the above tables as follow

 

 

To get the above output I need to first find the distinct subject that belong to distinct StudentId. To find the distinct subject we would make the used of CROSS APPLY along with VALUES function as below

;with StudentCTE as ( SELECT *,ROW_NUMBER() OVER (PARTITION BY StudentId Order by Subject) rn FROM StudentSubject CROSS APPLY (VALUES(Subject1),(Subject2)) dataset(Subject) )

 

The above CTE returns the result as below

 

Now to proceed to get the desired output of un-pivoting the above result set is simple and can be achieve by simple UNPIVOT , but there is another technique that we can used over here and that is as below

 

SELECT StudentId ,MAX(CASE WHEN rn=1 then Subject else null end) Subject1 ,MAX(CASE WHEN rn=2 then Subject else null end) Subject2 ,MAX(CASE WHEN rn=3 then Subject else null end) Subject3 ,MAX(CASE WHEN rn=4 then Subject else null end) Subject4 FROM StudentCTE GROUP BY StudentId

 

The above code gives us the desired result that we want.Need to careful that the above code would not work with join and only works with CROSS APPLY & OUTER APPLY. Hope this was usefull, in case of any questions do drop comment