0

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
Improve this question

1 Answer 1

Reset to default
0

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 $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]
        , 1 AS RC
    FROM [BCT_TDP_SURVEY_2018]
    WHERE [Bus_Fare_Payment_Method] != ''
    ) p
PIVOT(SUM(RC) 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]
Improve this answer
2
  • 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.
    – Ernesto CD
    Dec 3, 2021 at 15:26
  • 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)
    – Jonathan Fite
    Dec 3, 2021 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.