PIVOT clause in SQL Server
In SQL Server (Transact-SQL), the PIVOT clause allows cross tabulation to transfer data from one table to another, ie, get the aggregate result and move from line to column.
The example calculates the sum and then passes the rows into columns in the data table
PIVOT clause syntax
SELECT cot_dautien AS ,
[giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n]
FROM
() AS
PIVOT
(
ham_tong ()
FOR
IN ([giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n])
) AS n>;
) AS n>;
Variable name or variable value
cot_dautien
The column or expression will become the first column in the new table after the transition.
bidanh_cot_dautien
The name of the first column in the new table after the transfer.
giatri_chuyen1, giatri_chuyen2, . giatri_chuyen_n
List of values to transfer.
bang_nguon
The SELECT statement takes source data (initial data) into the new table.
bidanh_bang_nguon
The alias of bang_nguon
ham_tong
Sum functions such as SUM, COUNT, MIN, MAX or AVG.
cot_tong
The column or expression is used with ham_tong.
cot_chuyen
The column contains the value to be transferred.
bidanh_bang_chuyen
Alias of the table after transfer.
The PIVOT clause can be used in later versions of SQL Server: SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
To follow the steps in the tutorial, see the DDL section for creating tables and DML to create data at the end of this article and then try running on your own database.
Example with the PIVOT clause
We have the table with the data as shown below.
so_nhan_en_production id_phong 12009 Nguyen Huong 54000 45 34974 Pham Hoa 80000 45 34987 Phan Lan 42000 45 45001 Tran Hua 57500 30 75623 Vu Hong 65000 30Run the SQL command below to create a cross-query with the PIVOT clause.
SELECT 'TongLuong' AS TongLuongTheoPhong,
[30], [45]
FROM
(SELECT id_phong, luong
FROM nhanvien) AS BangNguon
PIVOT
(
SUM(luong)
FOR id_phong IN ([30], [45])
) AS BangChuyen;
The returned result will look like the following.
TongLuongTheoPhong 30 45 TongLuong 122500 176000The above example creates a table after the data has been transferred, indicating the total salary of the room has an ID of 30 and the room has an ID of 45. The result is on a row with 2 columns, each column is 1 room.
Specify the column in the new table of the cross-query
First need to determine which fields of information want to include in the transfer table. In this example, TongLuong is the first column, then 2 columns id_phong 30 and id_phong 45.
SELECT 'TongLuong' AS TongLuongTheoPhong,
[30], [
45]
Determine the data in the source table
Next is the SELECT statement that will return the source data for the new table.
In this example, it is id_phong and from the table.
(SELECT id_phong, luong
FROM nhanvien) AS Ba
ngNguon
Need to specify the alias for the source query, in this example is BangNguon.
Determine the total calculation function
Functions that can be used in cross queries include SUM, COUNT, MIN, MAX and AVG. In this example, the sum function SUM.
PIVOT
(SUM(luong)
Determine the value to be transferred
Finally, the value needs to be transferred to include the result. This will be the column header in the cross query.
In this example, we only need to return id_folder 30 and 45. These values will be the column names in the new table. Remember that these values are a list of limited values of id_phong and do not necessarily contain all values.
FOR id_phong IN ([30], [45])
DDL / DML for examples
If you have a database and want to try out the examples in the PIVOT instruction manual above, you will need DDL / DML.
DDL - Data Definition Language are table creation commands (CREATE TABLE) for use in the PIVOT clause example.
CREATE TABLE phong
( id_phong INT NOT NULL,
ten_phong VARCHAR(50) NOT NULL,
CONSTRAINT pk_phong PRIMARY KEY (id_phong)
) ;
CREATE TABLE nhanvien
( so_nhanvien INT NOT NULL,
ho VARCHAR(50) NOT NULL,
ten VARCHAR(50) NOT NULL,
luong INT,
id_phong INT,
CONSTRAINT pk_nhanvien PRIMARY KEY (so_nhanvien)
) ;
DML - Data Manipulation Language are INSERT statements to create the necessary data for the table.
INSERTINTO phong
(id_phong, ten_phong)
VALUES
(30, 'Ketoan
');
INSERT INTOphong
(id_phong, ten_phong)
VALUES
(45, 'Banhang');
INSERT INTOnhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(12009, 'Nguye
n', 'Huong', 54000, 45);
INSERT INTOnhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34974, 'Pham',
'Flowers', 80000, 45);
INSERT INTOanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34987, 'Phan', 'La
n', 42000, 45);
INSERTINTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
45001, 'Tr
an', 'Hue', 57500, 30);
INSERT INTO home
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(75623, 'Vu', 'Hong'
, 65000, 30);
Previous article: Query SUBQUERY child in SQL Server
The following article: Data types in SQL Server
You should read it
- How to Add Columns in a Pivot Table
- Use IIS to set up FTP Server on Windows
- Network basics: Part 3 - DNS Server
- How to set up your own Git server on Linux
- New points in SQL Server 2017
- Learn about the architecture of MS SQL Server
- Instructions for installing MS SQL Server
- How to change DNS server on the most popular routers
May be interested
- How to Create Pivot Tables in Excelpivot tables are interactive tables that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. they can sort, count, and total the data, and are available in a variety...
- How to calculate the percentage change in Pivot Table in Excelalthough the main function of a pivottable is to summarize large data, you can use them to calculate the percentage change between values.
- Use Pivot Table in the Google Docs Spreadsheetthis week, google docs has officially unveiled excel's pivot tables - a long-standing omission feature. this process is really useful when you have a spreadsheet with huge amounts of data, and you have to create general statistics, with a mechanism that works on rotation, retrieves information from many different angles. ..
- IF commands ... ELSE in SQL Serverlike other programming languages, sql server also provides users with an if command el .... the article will detail how to use the syntax and clear examples to make it easier for you to imagine if ... else.
- The clause to combine JOIN data in SQLin sql, the join clause is used to combine records from two or more tables in a database using common values from each table.
- Attach database in Microsoft SQL Server 2008this article will illustrate the different usage methods of the 'for attach' clause to overcome the limitations encountered when using sp_attach_db and sp_attach_single_file_db.
- HAVING clause in SQLthe having clause in sql is used to filter records and retrieve only those records that match the requirements or are actually needed.
- Interesting Facts About The Santa Clausethe santa clause is one of the best christmas movies of all time. however, behind this christmas movie are surprising, little-known facts about the making process.
- GROUP BY command in SQLthe group by clause in sql is used in conjunction with the select statement to sort data uniformly into groups.
- ORDER BY command in SQLin sql, the order by clause is used to sort data in ascending order or in descending order on one or more columns.