Which SQL function I should use to repeat existing row values for a new column

I have a table with 2 column studentID and StudentName and department.

StudenID  StudentName Department
1          john         IT
2          Michael      Finance
3          Josh         chemical

I would like to create a view based on Student table where I would like to have StudentID,StudentName and a Category Column that has 4 values as follows:

A,B,C,D

In the final view I would like to have above category assigned to each Student something like below

StudenID  StudentName Department   Category
1          john         IT         A
1          john         IT         B
1          john         IT         C
1          john         IT         D
2          Michael      Finance    A
2          Michael      Finance    B
2          Michael      Finance    C
2          Michael      Finance    D
3          Josh         chemical   A
3          Josh         chemical   B
3          Josh         chemical   C
3          Josh         chemical   D

Answer

Just use a cross join. In most databases you can do something like this:

select s.*, v.*
from students s cross join
     (values ('A'), ('B'), ('C'), ('D')) v(category);

Advertisement

Answer