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

Введение

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

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

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

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

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

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

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

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

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

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

Преобразование строк в пару столбцов при помощи оператора Pivot

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

  • Осуществите выборку данных при помощи следующего запроса:
/* Получение данных из таблицы */WITH cte_result AS(SELECT m.movieid, m.title, ROUND(r.rating,0) AS rating, u.genderFROM [movielens].[dbo].[rating] r JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieidJOIN [movielens].[dbo].[user] u ON u.userid=r.useridWHERE 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,genderORDER BY movieid,title,gender 
  • Выберите строки для преобразования в столбцы, как показано на диаграмме:
Сведение и разбиение нескольких столбцов в MS SQL Server

Строки могут быть преобразованы в пару столбцов при помощи применения обоих операторов(UNPIVOT и PIVOT).

  • Используйте оператор UNPIVOT, чтобы извлечь значения столбцов rating, nofuser и avgr и преобразовать их в один столбец с несколькими строками:
/* Получение агрегированных данных, используя Unpivot, и преобразование столбца в строку */WITH cte_result AS(SELECT m.movieid, m.title, ROUND(r.rating,0) AS rating, u.genderFROM [movielens].[dbo].[rating] r JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieidJOIN [movielens].[dbo].[user] u ON u.userid=r.useridWHERE 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) rtunpivot( value FOR col in(rating,nofuser,avgr))unpivORDER BY movieid

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

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

Оператор PIVOT применяется к полученному результату, чтобы преобразовать полученный столбец в пару строк.

  • Получите агрегированные данные, используя оператор pivot, и преобразуйте пару полей ввода в пару столбцов:
/* Получение агрегированных данных, используя Pivot, и преобразование нескольких полей ввода в пару столбцов */WITH cte_result AS(SELECT m.movieid, m.title, ROUND(r.rating,0) AS rating, u.genderFROM [movielens].[dbo].[rating] r JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieidJOIN [movielens].[dbo].[user] u ON u.userid=r.useridWHERE 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) rtunpivot( value FOR col in (rating,nofuser,avgr))unpiv )tppivot ( SUM(value) FOR col in ([M_rating],[M_nofuser],[M_avgr],[F_rating],[F_nofuser],[F_avgr])) piv ORDER BY movieid

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

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

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

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

Заключение

В данной статье мы обсудили MS SQL оператор pivot, предназначенный для преобразования данных из строк в столбцы. А также оператор unpivot, предназначенный для преобразования столбцов в строки.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *