#sql #sql-server #pivot
#sql #sql-сервер #стержень
Вопрос:
У меня возникли проблемы с созданием сводной таблицы, которая соответствует схеме, описанной ниже. Например, со следующей таблицей (здесь показаны не все записи):
| Маршрут | Метод Bus_Fare_Payment_Method | итого_аннальный_хранилищ_выход |
|---|---|---|
| Маршрут 1 | Пропуск на 10 Поездок | От $ 15 Тыс. До $19 Тыс. |
| Маршрут 1 | Пропуск на 10 Поездок | От $ 15 Тыс. До $19 Тыс. |
| Маршрут 1 | Пропуск на 10 Поездок | От $ 25 Тыс. До $29 Тыс. |
| Маршрут 1 | Пропуск на 10 Поездок | 60 Тысяч Долларов Или Больше |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 1 | Обычный Денежный Тариф | Менее 10 тысяч долларов |
| Маршрут 10 | Пропуск на 10 Поездок | От $ 30 Тыс. До $39 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 10 Тыс. До $14 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 10 Тыс. До $14 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 10 Тыс. До $14 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 10 Тыс. До $14 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 15 Тыс. До $19 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 20 Тыс. До $24 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 20 Тыс. До $24 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 20 Тыс. До $24 Тыс. |
| Маршрут 10 | 31-Дневный Взрослый | От $ 20 Тыс. До $24 Тыс. |
| Маршрут 101 | Весь Дневной Пропуск Сокращен | Менее 10 тысяч долларов |
| Route 101 | Other | Under $10K |
| Route 101 | Reduced Fare | $10K To $14K |
| Route 101 | Reduced Fare | $25K To $29K |
| Route 101 | Reduced Fare | $30K To $39K |
| Route 101 | Reduced Fare | $40K To $49K |
| Route 101 | Reduced Fare | $60K Or More |
| Route 101 | Reduced Fare | $60K Or More |
| Route 101 | Reduced Fare | $60K Or More |
| Route 101 | Reduced Fare | Under $10K |
| Route 101 | Reduced Fare | Under $10K |
| Route 101 | Reduced Fare | Under $10K |
| Route 101 | Regular Cash Fare | $10K To $14K |
| Route 101 | Regular Cash Fare | $10K To $14K |
| Route 101 | Regular Cash Fare | $10K To $14K |
| Route 101 | Regular Cash Fare | $10K To $14K |
I would like to produce the following table:
| Route | Bus_Fare_Payment_Method | $10K To $14K | $15K To $19K | $20K To $24K | $25K To $29K | $30K To $39K | $40K To $49K | $60K Or More | Under $10K |
|---|---|---|---|---|---|---|---|---|---|
| Route 1 | 10-Ride Pass | 2 | 1 | 1 | |||||
| Route 1 | 31-Day Adult | ||||||||
| Route 1 | All Day Pass Reduced | ||||||||
| Route 1 | Other | ||||||||
| Route 1 | Reduced Fare | ||||||||
| Route 1 | Regular Cash Fare | 8 | |||||||
| Route 10 | 10-Ride Pass | 1 | |||||||
| Route 10 | 31-Day Adult | 4 | 1 | 4 | |||||
| Route 10 | All Day Pass Reduced | ||||||||
| Route 10 | Other | ||||||||
| Route 10 | Reduced Fare | ||||||||
| Route 10 | Regular Cash Fare | ||||||||
| Route 101 | 10-Ride Pass | ||||||||
| Маршрут 101 | 31-Дневный Взрослый | ||||||||
| Маршрут 101 | Весь Дневной Пропуск Сокращен | 1 | |||||||
| Маршрут 101 | Другое | 1 | |||||||
| Маршрут 101 | Льготный Тариф | 1 | 1 | 1 | 1 | 3 | 3 | ||
| Маршрут 101 | Обычный Денежный Тариф | 4 |
Я могу создать следующую таблицу с включенным ниже запросом, но мне не хватает поля маршрута, которое мне нужно как часть моего вывода (как показано выше).
SELECT [Bus_Fare_Payment_Method] "Bus Fare Payment Method", [Under $10k] 'Under $10k', [$10K to $14K] '$10K to $14K',[$15k to $19k] '$15k to $19k', [$20k to $24k] '$20k to $24k', [$25k to $29k] '$25k to $29k', [$30k to $39k] '$30k to $39k', [$40k to $49k] '$40k to $49k', [$50k to $59k] '$50k to $59k', [$60k or more] '$60k or more' FROM (SELECT [Route], [Total_Annual_Household_Income], [Bus_Fare_Payment_Method] FROM [BCT_TDP_SURVEY_2018] where [Bus_Fare_Payment_Method] != '' ) p PIVOT ( COUNT ([Route]) FOR [Total_Annual_Household_Income] IN ( [Under $10k], [$10K to $14K],[$15k to $19k], [$20k to $24k], [$25k to $29k], [$30k to $39k], [$40k to $49k], [$50k to $59k], [$60k or more] ) ) AS pvt ORDER BY pvt.[Bus_Fare_Payment_Method] | Метод Bus_Fare_Payment_Method | От $ 10 Тыс. До $14 Тыс. | От $ 15 Тыс. До $19 Тыс. | От $ 20 Тыс. До $24 Тыс. | От $ 25 Тыс. До $29 Тыс. | От $ 30 Тыс. До $39 Тыс. | От $ 40 Тыс. До $49 Тыс. | $60K Or More | Under $10K |
|---|---|---|---|---|---|---|---|---|
| Пропуск на 10 Поездок | 2 | 1 | 1 | 1 | ||||
| 31-Дневный Взрослый | 4 | 1 | 4 | |||||
| Весь Дневной Пропуск Сокращен | 1 | |||||||
| Другое | 1 | |||||||
| Льготный Тариф | 1 | 1 | 1 | 1 | 3 | 3 | ||
| Обычный Денежный Тариф | 4 | 8 |
Комментарии:
1. Вы опустили его в своем
SELECT; конечно, его там не будет. Хотя я предлагаю использовать условную агрегацию вместо ограничительногоPivotоператора.2. @Larnu спасибо за ваш быстрый ответ. Я уже пробовал это, но безуспешно. Он возвращает следующую ошибку: Недопустимое имя столбца «Маршрут».
Ответ №1:
Как я уже упоминал в комментариях, вам гораздо лучше использовать условную агрегацию вместо ограничительного PIVOT оператора:
SELECT Route, Bus_Fare_Payment_Method, COUNT(CASE Total_Annual_Household_Income WHEN '$15K To $19K' THEN 1 END) AS [$15K To $19K], COUNT(CASE Total_Annual_Household_Income WHEN '$25K To $29K' THEN 1 END) AS [$25K To $29K], ... FROM dbo.YourTable GROUP BY Route, Bus_Fare_Payment_Method;