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

​x
 
StudenID  StudentName Department1          john         IT2          Michael      Finance3          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

 
StudenID  StudentName Department   Category1          john         IT         A1          john         IT         B1          john         IT         C1          john         IT         D2          Michael      Finance    A2          Michael      Finance    B2          Michael      Finance    C2          Michael      Finance    D3          Josh         chemical   A3          Josh         chemical   B3          Josh         chemical   C3          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);

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

Advertisement

Answer