Возможно ли иметь несколько сводок, используя один и тот же столбец сводки, используя SQL Server

Я сталкиваюсь со следующей задачей. Мне нужно дважды повернуть данные таблицы по одному и тому же столбцу. Вот скриншот данных.

Список предметов с указанием стоимости покупки и продажи за каждый год продажи предметов

Я хочу иметь одну строку для каждого идентификатора товара, содержащую как стоимость покупки, так и стоимость продажи за каждый год. Я попытался сделать это, дважды выбрав столбец «год», немного отформатировав его, чтобы каждый год продажи начинался с префикса «S», а каждый год покупки начинался с «P», и использовал 2 поворота для поворота вокруг столбцов за 2 года. . Вот запрос SQL (используется в SQL Server 2008):

SELECT [Item ID], 
        [P2000],[P2001],[P2002],[P2003],
        [S2000],[S2001],[S2002],[S2003]
FROM 
(

SELECT [Item ID]
      ,'P' + [Year] AS YearOfPurchase
      ,'S' + [Year] AS YearOfSelling

  ,[Purchasing value]
  ,[Selling value]
  FROM [ItemPrices]
) AS ALIAS

PIVOT 
(
MIN ([Purchasing value]) FOR [YearOfPurchase] in ([P2000],[P2001],[P2002],[P2003])
)
AS pvt

PIVOT 
(
MIN ([Selling value]) FOR [YearOfSelling] in ([S2000],[S2001],[S2002],[S2003])
)
AS pvt2

Результат не совсем тот, на который я рассчитывал (см. изображение ниже):

Реальная ситуация: слишком много строк

Как видите, для каждого идентификатора элемента по-прежнему существует более одной строки. Есть ли способ уменьшить количество строк ровно до одной на элемент? Чтобы это выглядело немного похоже на скриншот Excel ниже?

Желаемая ситуация: одна строка для каждого идентификатора элемента


sql sql-server unpivot pivot
person Rob Vermeulen    schedule 07.03.2013    source источник
comment
Именно тот вопрос, который мне был нужен - собирался задать что-то подобное сам!   -  person JosephDoggie    schedule 30.04.2019

Ответы (3)


arrow_upward
30
arrow_downward

Мое предложение состояло бы в том, чтобы применить обе функции UNPIVOT и PIVOT, чтобы получить результат.

UNPIVOT превратит столбцы PurchasingValue и SellingValue в строки. Как только это будет сделано, вы можете преобразовать данные в свой результат.

Код будет:

select *
from
(
  select itemid, 
    case 
      when col = 'PurchasingValue' then 'P'
      when col = 'SellingValue' then 'S'
    end + cast(year as varchar(4)) new_col,
    value
  from yourtable
  unpivot
  (
    value
    for col in ([PurchasingValue], [SellingValue])
  ) unpiv
) src
pivot
(
  max(value)
  for new_col in (P2000, P2001, P2002, P2003,
                  S2000, S2001, S2002, S2003)
) piv;

См. SQL Fiddle с демонстрацией. Результат:

| ITEMID | P2000 | P2001 | P2002 | P2003 | S2000 | S2001 | S2002 | S2003 |
--------------------------------------------------------------------------
|      1 |  1000 |  1100 |  1200 |  1300 |   900 |   990 |  1080 |  1170 |
|      2 |   500 |   550 |   600 |   650 |   450 |   495 |   540 |   585 |

В SQL Server 2008+ вы можете использовать CROSS APPLY с VALUES вместе с функцией PIVOT:

select *
from
(
  select itemid,
    col+cast(year as varchar(4)) new_col,
    value
  from yourtable
  cross apply
  (
    VALUES
        (PurchasingValue, 'P'),
        (SellingValue, 'S')
   ) x (value, col)
) src
pivot
(
  max(value)
  for new_col in (P2000, P2001, P2002, P2003,
                  S2000, S2001, S2002, S2003)
) piv

См. SQL Fiddle с демонстрацией.

person Taryn    schedule 07.03.2013
comment
Спасибо за предложение unpivot. Это не приходило мне в голову (и не уверен, что придет в будущем :-)) - person Rob Vermeulen; 08.03.2013
comment
@RobVermeulen Да, функция разворота отлично работает. Я только что обновил свой ответ версией, в которой также используется cross apply. Оба получат одинаковый результат. - person Taryn; 08.03.2013
comment
Будет ли перекрестное применение быстрее? - person Rob Vermeulen; 29.10.2013
comment
@RobVermeulen Трудно сказать, это действительно зависит от многих факторов. Если вас беспокоит скорость, вам следует попробовать разные варианты запроса, чтобы определить, что лучше всего подходит для вашей ситуации. - person Taryn; 29.10.2013
comment
я знаю, что сообщения с благодарностью нежелательны, но я просто должен был сказать вам большое спасибо! вы спасли мою неделю, сэр. до того, как я добрался до этого сообщения, единственным решением, которое я нашел, было дважды выполнить поворот на одной и той же таблице, а затем присоединиться к ним, но по какой-то причине второй поворот выполнялся на декартовом произведении соединения, а не на самой таблице, поэтому, как только количество строк превысило 1000, время выполнения взлетело до небес. так что только узнав о существовании unpivot (благодаря вам), я смог решить свою проблему. ваше здоровье! - person Souhaieb Besbes; 07.05.2015
comment
Когда я смотрю на этот ответ для собственного использования, буду ли я прав, если скажу, что этот метод можно использовать только до тех пор, пока функция агрегирования, которую вы планируете применять к обоим типам значений, одинакова? Допустим, вы хотели SUM стоимость покупки, но вы хотели AVG стоимость продажи. Я полагаю, что лучшим ответом было бы придерживаться кода OP и использовать логику, которую предлагает @pieterGeerkens? - person JHixson; 22.09.2017
comment
@JHixson, если вам нужно использовать разные типы агрегации, вам следует использовать выражение case вместе с агрегатной функцией, аналогичной ответу JamieD77. - person Taryn; 22.09.2017
comment
В другой раз вы не можете использовать это, когда два столбца, которые вы хотите развернуть, относятся к разным типам :(. Если вы пойдете по этому пути, вы получите ошибку, например The type of column "some_float_column" conflicts with the type of other columns specified in the UNPIVOT list. - person Alain; 17.12.2020
comment
@Alain, если вы используете версию CROSS APPLY, вы можете преобразовать значения в те же типы данных. - person Taryn; 17.12.2020

arrow_upward
10
arrow_downward

Один простой способ повернуть несколько столбцов — просто использовать выражения Aggregate(Case).

SELECT  [Item ID],
        [P2000] = SUM(CASE WHEN [Year] = 2000 THEN [Purchasing value] END),
        [P2001] = SUM(CASE WHEN [Year] = 2001 THEN [Purchasing value] END),
        [P2002] = SUM(CASE WHEN [Year] = 2002 THEN [Purchasing value] END),
        [P2003] = SUM(CASE WHEN [Year] = 2003 THEN [Purchasing value] END),
        [S2000] = SUM(CASE WHEN [Year] = 2000 THEN [Selling value] END),
        [S2001] = SUM(CASE WHEN [Year] = 2001 THEN [Selling value] END),
        [S2002] = SUM(CASE WHEN [Year] = 2002 THEN [Selling value] END),
        [S2003] = SUM(CASE WHEN [Year] = 2003 THEN [Selling value] END)
FROM    ItemPrices
GROUP BY [Item ID]
person JamieD77    schedule 01.09.2016

arrow_upward
5
arrow_downward

Используйте GROUP BY ItemID с агрегатной функцией SUM(isnull(value,0)) для каждого из столбцов результатов.

person Pieter Geerkens    schedule 07.03.2013