Dfyjkt

I have a query that returns an entire row and I need to pivot this result into a new table.

SELECT id_no, stud_name, group_no, class_1, class_2, class_3, class_4 FROM tbl_stud_class

This returns the following:

| id_no | stud_name | group_no | class_1 | class_2 | class_3 | class 4 |
| 1 | John Doe | A11 | 84 | 60 | 80 | 79 |

I need to be able to return this row as:

| id_no | stud_name | group_no | class | grade |
| 1 | John Doe | A11 | class_1 | 84 |
| 1 | John Doe | A11 | class_2 | 60 |
| 1 | John Doe | A11 | class_3 | 80 |
| 1 | John Doe | A11 | class_4 | 79 |

Can someone point me to a way to do this please?
I am converting my PostgreSQL function (where i'm using CROSS JOIN LATERAL to SQL Server)

Thank you!

Just another option is using a CROSS APPLY

Example

Select A.id_no
 ,A.stud_name
 ,A.group_no
 ,B.*
 From YourTable A
 Cross Apply ( values ('class_1',class_1)
 ,('class_2',class_2)
 ,('class_3',class_3)
 ,('class_4',class_4)
 ) B(class,grade)

Returns

id_no stud_name group_no class grade
1 John Doe A11 class_1 84
1 John Doe A11 class_2 60
1 John Doe A11 class_3 80
1 John Doe A11 class_4 79

You can use UNPIVOT to get this done in SQL Server. Below is an example using your sample data.

CREATE TABLE #tbl_stud_class
(
 id_no int,
 stud_name varchar(50),
 group_no varchar(50),
 class_1 int,
 class_2 int,
 class_3 int,
 class_4 int
)

INSERT INTO #tbl_stud_class VALUES (1, 'John Doe', 'A11', 84, 60, 80, 79)

SELECT * 
FROM #tbl_stud_class
UNPIVOT
(
 Class FOR Classes IN (class_1, class_2, class_3, class_4)
) AS UP

DROP TABLE #tbl_stud_class

Based on your example, I'd do something like this:

Select
 id_no
 , stud_name
 , group_no
 , 'class_1'
 , class_1 as grade
From tbl_stud_class

Union All

Select
 id_no
 , stud_name
 , group_no
 , 'class_2'
 , class_2 as grade
From tbl_stud_class

Union All

etc.

I would also use the full word "student" in my table and field names, but that's not really a database issue... ;)