How to pivot using multiple columns in SQL Server?

Question

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

I would like to produce the following table:

Route	Bus_Fare_Payment_Method	10ドルK To 14ドルK	15ドルK To 19ドルK	20ドルK To 24ドルK	25ドルK To 29ドルK	30ドルK To 39ドルK	40ドルK To 49ドルK	60ドルK Or More	Under 10ドルK
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 10ドルk] 'Under 10ドルk', [10ドルK to 14ドルK] '10ドルK to 14ドルK',[15ドルk to 19ドルk] '15ドルk to 19ドルk', [20ドルk to 24ドルk] '20ドルk to 24ドルk', [25ドルk to 29ドルk] '25ドルk to 29ドルk', [30ドルk to 39ドルk] '30ドルk to 39ドルk', [40ドルk to 49ドルk] '40ドルk to 49ドルk', [50ドルk to 59ドルk] '50ドルk to 59ドルk', [60ドルk or more] '60ドルk 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 10ドルk], [10ドルK to 14ドルK],[15ドルk to 19ドルk], [20ドルk to 24ドルk], [25ドルk to 29ドルk], [30ドルk to 39ドルk], [40ドルk to 49ドルk], [50ドルk to 59ドルk], [60ドルk or more] ) 
) AS pvt 
ORDER BY pvt.[Bus_Fare_Payment_Method]

Bus_Fare_Payment_Method	10ドルK To 14ドルK	15ドルK To 19ドルK	20ドルK To 24ドルK	25ドルK To 29ドルK	30ドルK To 39ドルK	40ドルK To 49ドルK	60ドルK Or More	Under 10ドルK
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

Answer 1

If you could include a dbfiddle or something with the demo data that would be great. But in the meantime, I think this should work?

I added a column "1 AS RC" to count against so you can get the Route column back out. You can absolutely use multiple columns as the anchor for your pivot statements, you just need to use something else for the aggregate.

SELECT [Route] 
 , [Bus_Fare_Payment_Method] "Bus Fare Payment Method"
 , [Under 10ドルk] 'Under 10ドルk'
 , [10ドルK to 14ドルK] '10ドルK to 14ドルK'
 , [15ドルk to 19ドルk] '15ドルk to 19ドルk'
 , [20ドルk to 24ドルk] '20ドルk to 24ドルk'
 , [25ドルk to 29ドルk] '25ドルk to 29ドルk'
 , [30ドルk to 39ドルk] '30ドルk to 39ドルk'
 , [40ドルk to 49ドルk] '40ドルk to 49ドルk'
 , [50ドルk to 59ドルk] '50ドルk to 59ドルk'
 , [60ドルk or more] '60ドルk or more'
FROM (
 SELECT [Route]
 , [Total_Annual_Household_Income]
 , [Bus_Fare_Payment_Method]
 , 1 AS RC
 FROM [BCT_TDP_SURVEY_2018]
 WHERE [Bus_Fare_Payment_Method] != ''
 ) p
PIVOT(SUM(RC) FOR [Total_Annual_Household_Income] IN (
 [Under 10ドルk]
 , [10ドルK to 14ドルK]
 , [15ドルk to 19ドルk]
 , [20ドルk to 24ドルk]
 , [25ドルk to 29ドルk]
 , [30ドルk to 39ドルk]
 , [40ドルk to 49ドルk]
 , [50ドルk to 59ドルk]
 , [60ドルk or more]
 )) AS pvt
ORDER BY pvt.[Bus_Fare_Payment_Method]

• Ernesto CD: It worked perfectly! I was also told that I could I achieve this by using conditional aggregation. Thanks a gain for your time and sharing your knowledge.
• Jonathan Fite: I'm glad it worked for you. This dba.stackexchange.com/questions/164835/… seems to indicate that SQL uses conditional aggregation behind the scenes and well written versions of either perform equally well. So use whichever syntax you are most comfortable with (I like PIVOT, I think it looks cleaner)