CODEWITHSUNDEEP
sql-serverpivot
Smiley
Smiley

Reputation: 3437

Pivot Columns to Rows in SQL Server

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!

Upvotes: 3

Views: 1321

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 5

Chris Albert
Chris Albert

Reputation: 2507

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

Upvotes: 1

Brian
Brian

Reputation: 1248

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... ;)

Upvotes: 0

Related Questions