SQL Turn 1 поле, несколько записей в 1 запись, несколько полей

Question

Если бы я был более опытным, я мог бы использовать подобные вопросы "слияния строк", чтобы понять это, но я не могу заставить их работать. У меня есть около 2000 "гаражей" в аккредитованных группах с определенными категориями (на самом деле это клиники с данными пациентов, но здесь они упрощены). Ниже приведен пример того, как существуют данные для одного гаража:

Garage ID   Car_Color        Sub_Type?   Color_Count
    42       Blue            Striped        5
    42       Blue                           10
    42       Red                            23
    42       Yellow                         2

Мне нужно сделать каждый гараж в одной записи с каждым цветом в качестве отдельного поля с подсчетами, желаемый результат показан здесь:

Garage ID   Blue Cars (total)  Blue Cars (striped)   Red Cars  Yellow Cars   Orange Cars
    42         15                    5                  23          2             0

Существует одна категория с подтипом, показанная здесь как "Синие автомобили" и "Синие автомобили (в полоску)". Для этого мне нужно было бы сложить счетчик "Blue" без подтипа с другим счетчиком "Blue", чтобы получить общее количество "Blue". Затем я бы перечислил подтип Blue как отдельное поле. В большинстве гаражей нет оранжевых автомобилей, поэтому мне нужно, чтобы запись показывала 0 для этого поля, когда нет записей о оранжевых автомобилях, но отражала бы количество, если для этого есть запись.

Это похоже на другие самостоятельные объединения, но я не могу заставить работать счетчик или "0" для записи.

Answer 1

Вы можете использовать агрегатную функцию с IIF() выражение:

select [Garage ID],
  sum(IIF(Car_color='Blue', Color_Count, 0)) as BlueCarsTotal,
  sum(IIF(Car_color='Blue' AND [Sub_Type?]='Striped', Color_Count, 0)) as BlueCarsStripedTotal,
  sum(IIF(Car_color='Red', Color_Count, 0)) as RedCarsTotal,
  sum(IIF(Car_color='Yellow', Color_Count, 0)) as YellowCarsTotal,
  sum(IIF(Car_color='Orange', Color_Count, 0)) as OrangeCarsTotal
from yourtable
group by [Garage ID]

Если вы делали это в другой базе данных, которая позволяет CASE Выражения запроса будут:

select [Garage ID],
  sum(case 
       when Car_color='Blue' 
       then Color_Count else 0 end) as BlueCarsTotal
  sum(case 
       when Car_color='Blue' AND [Sub_Type?]='Striped' 
       then Color_Count else 0 end) as BlueCarsStripedTotal,
  sum(case 
       when Car_color='Red' 
       then Color_Count else 0 end) as RedCarsTotal,
  sum(case 
       when Car_color='Yellow' 
       then Color_Count else 0 end) as YellowCarsTotal,
  sum(case 
       when Car_color='Orange' 
       then Color_Count else 0 end) as OrangeCarsTotal
from yourtable
group by [Garage ID]

Answer 2

Кросс-таблица может подойти:

TRANSFORM Sum(Garages.Color_Count) AS SumOfColor_Count
SELECT Garages.GarageID
FROM Garages
GROUP BY Garages.GarageID
PIVOT [Car_Color] & " " & [sub_type?];