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
