Сведение и разбиение нескольких столбцов в MS SQL Server

Содержание

Введение

Оператор PIVOT используется для преобразования значений строк в значения столбцов. Оператор UNPIVOT используется для обратного преобразования. В этой статье мы рассмотрим преобразование строк в столбцы (PIVOT) и столбцов в строки (UNPIVOT) в MS SQL Server.

Системные требования

  • Установленный MS SQL SERVER 2012.
  • База данных MovieLens.

Пример использования

Мы преобразуем строки в столбец с помощью запроса агрегирования данных временной таблицы.

Описание базы данных

В этом примере используется пример базы данных, которая содержит информацию о фильмах и их пользовательских рейтингах. Модель данных, используемая в этом примере:

Описание базы данных
SQL Server в вопросах и ответах

Синтаксис 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;
Создание и удаление БД в MS SQL Server
  • Получите агрегированные данные, используя оператор 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

Строка, преобразованная в несколько столбцов, показана на следующей диаграмме:

Преобразование одной строки в несколько столбцов с помощью оператора Pivot

Преобразованные рейтинги фильмов, представленные графически в MS Excel:

Преобразование одной строки в несколько столбцов с помощью оператора Pivot - 2

Преобразование строк в несколько столбцов с помощью оператора 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&nbsp;
GROUP BY movieid,title,gender
ORDER BY movieid,title,gender
Виртуальная реализация SQL Server 2005
  • Выберите строки для преобразования в столбцы, как показано на диаграмме:
Преобразование строк в несколько столбцов с помощью оператора Pivot

Строки могут быть преобразованы в несколько столбцов с помощью применения обоих операторов (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 - 2

Оператор 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, предназначенный для преобразования столбцов в строки.

Пожалуйста, опубликуйте ваши отзывы по текущей теме статьи. Мы крайне благодарны вам за ваши комментарии, отклики, дизлайки, подписки, лайки!