SQL Pivot для использования в представлении
Я пытаюсь создать представление, которое суммирует общее количество часов различных типов работы, а также количество различных продуктов, используемых для RefNumber.
Я успешно развернул и суммировал часы работы, сгруппированные по RefNumber, но теперь я ломаю голову над тем, как суммировать общее использование продукта по RefNumber.
Имейте в виду, что на некоторых RefNumber будет несколько записей одного и того же ProductID. Возможно, несколько человек использовали один и тот же продукт в разных количествах.
Вот что я тоже пытаюсь развернуть:
До сих пор мне удавалось суммировать часы каждого типа работы и сводить эти данные в одну строку, сгруппированную по ссылочному номеру, используя следующий код SQL:
SELECT RefNumber,
SUM (CASE WHEN WorkType = 'Blast' THEN THours ELSE NULL END) AS TBlast,
SUM (CASE WHEN WorkType = 'Wheel' THEN THours ELSE NULL End) As TWheel,
SUM (CASE WHEN WorkType = 'Painting' THEN THours ELSE NULL END) AS TPainter,
SUM (CASE WHEN WorkType = 'Mask/Prep' THEN Thours ELSE NULL END) As TMask,
SUM (CASE WHEN WorkType = 'Demask/Touch Up' THEN Thours ELSE NULL END) As TDMask,
SUM (CASE WHEN WorkType = 'Handling: Raw' THEN THours ELSE NULL END) As TRHand,
SUM (CASE WHEN WorkType = 'Handling: Product' THEN Thours ELSE NULL END) As TPHand,
SUM (CASE WHEN WorkType = 'Wheel:Assist' THEN Thours ELSE NULL END) As TAWheel,
SUM (CASE WHEN WorkType = 'Metalizing' THEN Thours ELSE NULL END) As TMetal
FROM (
SELECT [RefNumber], [WorkType], SUM (Hours)/60 As THours
FROM [dbo].[Vw_Beta_CostLog]
GROUP BY RefNumber, WorkType
) sub
GROUP BY RefNumber
ORDER BY RefNumber
Любые идеи о том, как изменить эту базу кода, чтобы переместить отдельные идентификаторы продукта в собственный столбец и суммировать использование этих продуктов во втором столбце?
Кроме того, я хочу использовать это как представление, поэтому я стараюсь избегать динамических поворотов.
Обновлено: Забыл упомянуть, что для каждого номера ссылки будет использоваться не более 4 уникальных продуктов.
НЕОБРАБОТАННЫЕ ДАННЫЕ
GUID EmpName RefNumber DateInt Hours WorkType ProductID PQty
P-3468 Gary Hahn 114204 20181008 132 Painting NULL NULL
P-3473 Gary Hahn 114204 20181009 204 Painting NULL NULL
P-3475 Gary Hahn 114204 20181009 120 Painting NULL NULL
F-31915 Jose Flores 114204 20181007 60 Handling: Raw NULL NULL
F-31941 Jose Flores 114204 20181008 30 Handling: Raw NULL NULL
F-31951 Chris Pollock 114204 20181008 30 Handling: Raw NULL NULL
F-32076 Chris Pollock 114204 20181010 120 Handling: Product NULL NULL
F-32109 Chris Pollock 114204 20181011 90 Handling: Product NULL NULL
F-32301 Daryl Underwood 114204 20181015 15 Handling: Product NULL NULL
B-6594 David Martinez 114204 20181007 150 Blast NULL NULL
B-6599 Emiliano Barrios 114204 20181008 66 Blast NULL NULL
B-6617 Jose Molina 114204 20181009 30 Blast NULL NULL
P-3468 Gary Hahn 114204 20181008 NULL Primer 11 3
P-3473 Gary Hahn 114204 20181009 NULL Intermediate 890 2
P-3475 Gary Hahn 114204 20181009 NULL Finish 134HG 2
Результат, который я ищу
RefNumber Blast Painting Handling: Raw Handling: Product Product1 P1Qty Product2 P2Qty Product3 P3Qty
114204 246 456 120 225 11 3 890 2 134HG 2
Есть ли предпочтительный способ обмена образцами данных? Я просто собираюсь загрузить файл Excel в Dropbox, если вы не знаете лучшего способа.
В идеале вы должны предоставить свои образцы данных (простой случай подойдет) вместе с ожидаемым результатом, введенным в вопросе, вместо этих ссылок на изображения и другие веб-сайты, b / c некоторые из нас находятся в местах, где запрещены ссылки или изображения Dropbox. Тем не менее, показ вашей попытки запроса - это хорошо.
Если израсходовано 10 частей, вам нужно 20 столбцов? или, израсходовав 50 частей, вам нужно 100 колонок? В представлении должен быть фиксированный набор столбцов.
Будет использовано не более 4 продуктов, поэтому мне просто нужно преобразовать записи продуктов в общий Prod1 Prod2 Prod3, а затем просуммировать количество этих записей.
Ответы 1
Вы можете «повернуть» нечисловые данные, используя MAX() вместо SUM(), и продолжать использовать выражения case, как вы уже делаете.
SELECT
RefNumber
, SUM (CASE WHEN WorkType = 'Blast' THEN Hours/60 ELSE NULL END) AS TBlast
, SUM (CASE WHEN WorkType = 'Wheel' THEN Hours/60 ELSE NULL End) As TWheel
, SUM (CASE WHEN WorkType = 'Painting' THEN Hours/60 ELSE NULL END) AS TPainter
, SUM (CASE WHEN WorkType = 'Mask/Prep' THEN Hours/60 ELSE NULL END) As TMask
, SUM (CASE WHEN WorkType = 'Demask/Touch Up' THEN Hours/60 ELSE NULL END) As TDMask
, SUM (CASE WHEN WorkType = 'Handling: Raw' THEN Hours/60 ELSE NULL END) As TRHand
, SUM (CASE WHEN WorkType = 'Handling: Product' THEN Hours/60 ELSE NULL END) As TPHand
, SUM (CASE WHEN WorkType = 'Wheel:Assist' THEN Hours/60 ELSE NULL END) As TAWheel
, SUM (CASE WHEN WorkType = 'Metalizing' THEN Hours/60 ELSE NULL END) As TMetal
, MAX (CASE WHEN rn = 1 THEN WorkType END) As WorkType1
, MAX (CASE WHEN rn = 1 THEN ProductID END) As Product1
, MAX (CASE WHEN rn = 1 THEN PQty END) As Qty1
, MAX (CASE WHEN rn = 2 THEN WorkType END) As WorkType2
, MAX (CASE WHEN rn = 2 THEN ProductID END) As Product2
, MAX (CASE WHEN rn = 2 THEN PQty END) As Qty2
, MAX (CASE WHEN rn = 3 THEN WorkType END) As WorkType3
, MAX (CASE WHEN rn = 3 THEN ProductID END) As Product3
, MAX (CASE WHEN rn = 3 THEN PQty END) As Qty3
FROM (
SELECT
[RefNumber]
, [WorkType]
, [ProductID]
, Hours
, PQty
, ROW_NUMBER() OVER (PARTITION BY RefNumber, case when ProductID IS NULL then 0 else 1 end ORDER BY ProductID) rn
FROM [Vw_Beta_CostLog]
) sub
GROUP BY
RefNumber
ORDER BY
RefNumber
Обратите внимание, что вы не мочь создаете представление, которое динамически увеличивает или уменьшает количество столбцов в зависимости от того, сколько продуктов используется.
см. онлайн-демонстрацию: https://rextester.com/SBQUP24417
Я пробовал ваше решение Used_BY и получаю недопустимые имена столбцов. Я изменил product_id на ProductID и Qty на PQty в соответствии с моей схемой, но он по-прежнему не работает. Я попытался добавить ProductID и PQty в подзапрос from, но затем получаю ошибки агрегирования.
запрос был изменен, попробуйте еще раз
Не могли бы вы предоставить данные формата вместо изображения? Благодарность