Преобразование Pivot с использованием t-sql [закрыто]

В SSIS есть задача под названием Pivot transformation, которая преобразует столбцы в строки, но как выполнить ту же задачу в sql server с помощью t-sql ?

Это мой образец таблицы 

location product qty
-----------------------
delhi     PEPSI   100
GURGAON   CAKE    200
NOIDA     APPLE   150
delhi     cake    250

Таким образом, после преобразования pivot в location как setkey и product как pivot key с помощью инструмента ssis o/p становится

location pepsi cake apple
delhi     100 null null
GURGAON   null 200 null 
NOIDA     null null 150 
delhi     null 250  null
2 9
sql tsql sql-server-2008 ssis business-intelligence

2 ответа:

Используйте PIVOT табличный оператор типа этого: 

SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN([pepsi], [cake], [apple])
) as p;

Обратите внимание, что:

  • Я использовал агрегатную функцию MAX с функцией qty, Если вы хотите получить общую сумму, используйте вместо нее SUM или любую другую агрегатную функцию.

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

Вот так: 

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(product)
                      FROM tablename
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
FROM tablename
PIVOT
(
  MAX(qty)
  FOR product IN(' + @cols + ')) AS p;';

execute(@query);
9

Сводный запрос включает в себя три этапа логической обработки, каждый из которых содержит связанные элементы:

  1. фаза группировки
  2. фаза распространения
  3. и фаза агрегации с соответствующим элементом агрегации и агрегатной функцией.

Итак, сопоставление этих фаз с требованием в вашем случае:

  1. группировка должна выполняться по ‘Location’
  2. распространение должно осуществляться на основе значений столбцов ‘Product’ с конечными именами столбцов в виде: "Пепси", "Торт", "Яблоко".
  3. ‘Qty’ значения должны быть агрегированы для получения пересекающихся значений для группировки и распространения элементов

Ввод этих значений в стандартную сводную инструкцию: 

SELECT ...
FROM <source_table_or_table_expression>
PIVOT(<agg_func>(<aggregation_element>)
FOR <spreading_element>
IN (<list_of_target_columns>)) AS <result_table_alias>

Ваш запрос становится: 

select location ,[PEPSI], [CAKE],[APPLE]
from table1
pivot (sum(qty)
       for product
       in ( [PEPSI], [CAKE],[APPLE])) AS T

Важно отметить,что с помощью оператора PIVOT вы явно не указываете элементы группировки, устраняя необходимость в группировании ПО в запросе. Оператор PIVOT вычисляет группировку элементы неявно, как и все атрибуты из исходной таблицы (или табличного выражения), которые не были указаны ни как элемент распространения, ни как элемент агрегации. Таким образом, необходимо убедиться, что исходная таблица для оператора PIVOT не имеет атрибутов,кроме элементов группировки, распространения и агрегации, так что после указания элементов распространения и агрегации остаются только те атрибуты, которые вы намереваетесь использовать в качестве элементов группировки. Вы достигнете этого, не применяя оператор PIVOT непосредственно к исходной таблице, а вместо этого табличное выражение, включающее только атрибуты, представляющие поворотные элементы, и никакие другие. 

select location ,[PEPSI], [CAKE],[APPLE]
from (select location,product,qty 
      from table1 ) as SourceTable
      pivot (sum(qty)
           for product
           in ( [PEPSI], [CAKE],[APPLE])) AS T

Надеюсь, это поможет лучше понять оператора Pivot!!

EDIT: добавлена концепция оператора Unpivot:

Как и вращение, Непрививание также включает в себя 3 логические фазы:

  1. производство копий
  2. извлечение элементов
  3. устранение ненужных пересекающихся записей

Подставив эти значения в стандартное преобразование заявление: 

SELECT ...
FROM <source_table_or_table_expression>
UNPIVOT(<target_col_to_hold_source_col_values>
FOR <target_col_to_hold_source_col_names> IN(<list_of_source_columns>)) AS
<result_table_alias>;

Сопоставление этих фаз с требованием в вашем случае:

  1. <target_col_to_hold_source_col_values> = имя столбца, который будет содержать значения исходного столбца то есть: для хранения значений столбцов [Pepsi], [Cake],[Apple], то есть 100,250 ... вы хотите иметь один столбец в виде: Qty
  2. <target_col_to_hold_source_col_names> = имя столбца, который будет содержать имена исходных столбцов. то есть: для хранения имен столбцов [Pepsi], [Cake],[Apple] вы хотите иметь один единственный столбец в виде: product
  3. <list_of_source_columns> = имена столбцов в исходной таблице, которые вас интересуют в то есть: [Pepsi], [Cake],[Apple]

Ваш запрос становится: 

SELECT location,product,qty
FROM #temp
UNPIVOT(qty
        FOR product  
        IN([Pepsi],[Cake],[Apple])) AS U;

, где я добавил результаты из приведенного выше оператора Pivot во временную таблицу #temp.

Здесь важно отметить следующее: : Открепление сводной таблицы не может вернуть исходную таблицу, так как при ее откручивании теряется подробная информация из-за агрегирования.

8