Сведение и разбиение нескольких столбцов в MS SQL Server
Введение
Оператор PIVOT используется для преобразования значений строк в значения столбцов. Оператор UNPIVOT используется для обратного преобразования. В этой статье мы рассмотрим преобразование строк в столбцы (PIVOT) и столбцов в строки (UNPIVOT) в MS SQL Server.
Системные требования
- Установленный MS SQL SERVER 2012.
- База данных MovieLens.
Пример использования
Мы преобразуем строки в столбец с помощью запроса агрегирования данных временной таблицы.
Описание базы данных
В этом примере используется пример базы данных, которая содержит информацию о фильмах и их пользовательских рейтингах. Модель данных, используемая в этом примере:
Синтаксис Pivot
Синтаксис оператора pivot приведён ниже:
SELECT first_column AS <first_column_alias>,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM
(<source_table>) AS <source_table_alias>
PIVOT
(
aggregate_function(<aggregate_column>)
FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS <pivot_table_alias>; Параметры и аргументы
- first_column – поле или выражение, отображаемое как первый столбец сводной таблицы.
- first_column_alias – заголовок первого столбца сводной таблицы.
- pivot_value1, pivot_value2, … pivot_value_n – список сводных значений.
- source_table – выражение SELECT, предоставляющее исходные данные для исходной таблицы.
- source_table_alias – псевдоним исходной таблицы.
- aggregate_function – представляет агрегатные функции, такие как SUM, COUNT, MIN, MAX или AVG.
- aggregate_column – поле или выражение, используемое в агрегатной функции.
- pivot_column – поле, содержащее сводные значения.
- pivot_table_alias – псевдоним сводной таблицы.
Преобразование одной строки в несколько столбцов с помощью оператора Pivot
Чтобы преобразовать одну строку в несколько столбцов, выполните следующие действие:
- Осуществите выборку данных с помощью следующего запроса:
/* Получение данных из таблицы */
WITH cte_result AS(
SELECT
m.movieid ,m.title ,ROUND(r.rating,0) AS [rating],
CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
FROM [movielens].[dbo].[rating] r
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT * FROM (
SELECT
movieid AS [MovieId],
title AS [Movie Name],
CAST(COUNT(*) AS FLOAT) AS [noofuser],
CAST(SUM(Rating) AS FLOAT) AS [sumofrating],
CAST(AVG(Rating) AS FLOAT) AS [avgofrating],
CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
FROM cte_result WHERE MovieId <= 2 GROUP BY ROLLUP(movieid,title,star) )ratingfilter
WHERE [Movie Name] IS NOT NULL;- Получите агрегированные данные, используя оператор pivot, и преобразуйте одну строку в несколько столбцов с помощью следующего запроса:
/* Получение агрегированных данных с помощью pivot и преобразование поля в несколько столбцов */
WITH cte_result AS(
SELECT
m.movieid ,m.title ,ROUND(r.rating,0) AS [rating],
CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
FROM [movielens].[dbo].[rating] r
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT
[MovieId],
[Movie Name],
[1_rating],
[2_rating],
[3_rating],
[4_rating],
[5_rating],
[t_rating] FROM
(SELECT
movieid AS [MovieId] ,
title AS [Movie Name],
CAST(COUNT(*) AS FLOAT) AS [noofuser],
CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
FROM cte_result GROUP BY ROLLUP(movieid,title,star))ratingfilter
PIVOT (SUM([noofuser]) FOR [RatingGrade] IN ([1_rating],[2_rating],[3_rating],[4_rating],[5_rating],[t_rating]))a
WHERE [Movie Name] IS NOT NULL ORDER BY movieidСтрока, преобразованная в несколько столбцов, показана на следующей диаграмме:
Преобразованные рейтинги фильмов, представленные графически в MS Excel:
Преобразование строк в несколько столбцов с помощью оператора Pivot
Чтобы преобразовать несколько строк в несколько столбцов, выполните следующие действия:
- Осуществите выборку данных с помощью следующего запроса:
/* Получение данных из таблицы */
WITH cte_result AS(
SELECT
m.movieid,
m.title,
ROUND(r.rating,0) AS rating,
u.gender
FROM [movielens].[dbo].[rating] r
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result
GROUP BY movieid,title,gender
ORDER BY movieid,title,gender - Выберите строки для преобразования в столбцы, как показано на диаграмме:
Строки могут быть преобразованы в несколько столбцов с помощью применения обоих операторов (UNPIVOT и PIVOT).
- Используйте оператор UNPIVOT, чтобы извлечь значения столбцов rating, nofuser и avgr и преобразовать их в один столбец с несколькими строками:
/* Получение агрегированных данных, используя Unpivot, и преобразование столбца в строку */
WITH cte_result AS(
SELECT
m.movieid,
m.title,
ROUND(r.rating,0) AS rating,
u.gender
FROM [movielens].[dbo].[rating] r
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv
ORDER BY movieidПоля, преобразованные в один столбец, показаны на следующей диаграмме:
Оператор PIVOT применяется к полученному результату, чтобы преобразовать полученный столбец в несколько строк.
- Получите агрегированные данные, используя оператор pivot, и преобразуйте несколько полей в несколько столбцов:
/* Получение агрегированных данных, используя Pivot, и преобразование нескольких полей в несколько столбцов */
WITH cte_result AS(
SELECT
m.movieid,
m.title,
ROUND(r.rating,0) AS rating,
u.gender
FROM [movielens].[dbo].[rating] r
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,
[M_nofuser],[F_nofuser],
[M_rating],[F_rating],
[M_avgr],[F_avgr]
FROM
(
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv )tp
pivot ( SUM(value) FOR col in ([M_rating],[M_nofuser],[M_avgr],[F_rating],[F_nofuser],[F_avgr])) piv
ORDER BY movieidНесколько полей, преобразованные в несколько столбцов, показаны на следующей диаграмме:
Преобразованные рейтинги фильмов и их пользователи, графически представленные в MS Excel:
Заключение
В этой статье мы обсудили MS SQL оператор pivot, предназначенный для преобразования данных из строк в столбцы. А также оператор unpivot, предназначенный для преобразования столбцов в строки.