پست

استفاده از pivot در SQL Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE Grades
(
    [Student] NVARCHAR(50),
    [Subject] NVARCHAR(50),
    [Marks]   INT
)
GO

INSERT INTO Grades
VALUES ('ali', 'Mathematics', 20),
       ('ali', 'Science', 16),
       ('ali', 'Geography', 18),
       ('mohammad', 'Mathematics', 10),
       ('mohammad', 'Science', 18),
       ('mohammad', 'Geography', 17)
GO

1
2
3

ALTER TABLE Grades
    ADD
        DateOfEvent datetime2 NULL

1
2

INSERT INTO Grades (Student, Subject, Marks, DateOfEvent)
VALUES ('ali', 'Mathematics', 14, GETDATE())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE PROCEDURE dbo.DynamicPivotTableInSql @ColumnToPivot NVARCHAR(255),
                                            @ListToPivot   NVARCHAR(255)
AS
BEGIN
    SELECT *
    FROM (SELECT [Student],
                 [Subject],
                 [Marks]
          FROM Grades
         ) StudentResults
             PIVOT (
             AVG([Marks])
             FOR @ColumnToPivot
             IN (@ListToPivot)
             ) AS PivotTable
END
SQL
sql pivot sql_server