How to pivot using multiple columns in SQL Server?-CodePudding

I am having trouble creating pivot table that follows the schema described below. For example, with the following table (not all records are shown here):

Route	Bus_Fare_Payment_Method	Total_Annual_Household_Income
Route 1	10-Ride Pass	$15K To $19K
Route 1	10-Ride Pass	$15K To $19K
Route 1	10-Ride Pass	$25K To $29K
Route 1	10-Ride Pass	$60K Or More
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 1	Regular Cash Fare	Under $10K
Route 10	10-Ride Pass	$30K To $39K
Route 10	31-Day Adult	$10K To $14K
Route 10	31-Day Adult	$10K To $14K
Route 10	31-Day Adult	$10K To $14K
Route 10	31-Day Adult	$10K To $14K
Route 10	31-Day Adult	$15K To $19K
Route 10	31-Day Adult	$20K To $24K
Route 10	31-Day Adult	$20K To $24K
Route 10	31-Day Adult	$20K To $24K
Route 10	31-Day Adult	$20K To $24K
Route 101	All Day Pass Reduced	Under $10K
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
Route 101	31-Day Adult
Route 101	All Day Pass Reduced								1
Route 101	Other								1
Route 101	Reduced Fare	1			1	1	1	3	3
Route 101	Regular Cash Fare	4

I am able to create the following table with the query included below, but I am missing the Route field which I need as part of my output (as shown above).

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	$10K To $14K	$15K To $19K	$20K To $24K	$25K To $29K	$30K To $39K	$40K To $49K	$60K Or More	Under $10K
10-Ride Pass		2		1	1		1
31-Day Adult	4	1	4
All Day Pass Reduced								1
Other								1
Reduced Fare	1			1	1	1	3	3
Regular Cash Fare	4							8

CodePudding user response：

As I mentioned in the comments, you are far better off using conditional aggregation over the restrictive PIVOT operator:

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;