CODEWITHSUNDEEP
sqlsql-server
Vic
Vic

Reputation: 55

Convert Rows to Columns in SQL Server - pivot

I did some readings on this, seems the best option would be to use the pivot function, but I'm still having trouble putting it to actual use.

I have simplified my code below:

SELECT o.order_id,  
       chr.charge_name,
       
       Sum(chr.amount) AS amount,
       
       
FROM   [tms].[orders] o
       INNER JOIN [tms].[extra_charges_details] chr
               ON o.sourcetms = chr.sourcetms
                  AND o.order_id = chr.[order]
       INNER JOIN [tms].[probills] p
               ON o.sourcetms = p.sourcetms
                  AND o.order_id = p.order_id
       INNER JOIN [tms].[customers] c on o.UniqueCustomerID=c.UniqueCompanyID
WHERE  o.[date] >= '2021-01-01'

GROUP  BY 
          o.order_id,
          chr.charge_name,

The output looks like this:

order_id charge_name amount
OCA0099128 FUEL 49.52
OCA0101663 FSC 0.113
OCA0101663 HRS HWY 40.85
OCA0101663 KM HIGHWAY 1.134
OCA0101663 MISC 89.3
OCA0101664 FSC 0.113
OCA0101664 HR CITY 40.85
OCA0101664 KM CITY 0.65
OCA0101665 FSC 0.093

and I want to make it like this:

Row Labels FSC FUEL HR CITY HRS HWY KM CITY KM HIGHWAY MISC
OCA0099128 49.52
OCA0101663 0.113 40.85 1.134 89.3
OCA0101664 0.113 40.85 0.65
OCA0101665 0.093

Any help/recommendation appreciated, thanks!

Upvotes: 0

Views: 121

Answers (2)

simon at rcl
simon at rcl

Reputation: 7344

Take your query as the heart of the pivot, but leave out the group by and the sum(). Surround it with the pivot:

select *
from
(
            SELECT o.order_id,  
                   chr.charge_name,
                   chr.charge_amount
            FROM   [tms].[orders] o
                   INNER JOIN [tms].[extra_charges_details] chr
                           ON o.sourcetms = chr.sourcetms
                              AND o.order_id = chr.[order]
                   INNER JOIN [tms].[probills] p
                           ON o.sourcetms = p.sourcetms
                              AND o.order_id = p.order_id
                   INNER JOIN [tms].[customers] c on o.UniqueCustomerID=c.UniqueCompanyID
            WHERE  o.[date] >= '2021-01-01'
) b
pivot (sum(b.charge_amount) for chr.charge_name in
                        (
                            [FSC], [HR CITY], [HRS HIGHWAY], 
                            [KM CITY], [KM HIGHWAY], [MISC]
                        )
) p

Obviously, the values of charge_code have to be hard-coded; but they do in any way you might try to do this.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think conditional aggregation is usually simpler:

SELECT o.order_id,  
       SUM(CASE WHEN chr.charge_name = 'FSC' THEN amount END) as fsc,
       SUM(CASE WHEN chr.charge_name = 'HR CITY' THEN amount END) as hr_city,
       SUM(CASE WHEN chr.charge_name = 'HRS HIGHWAY' THEN amount END) as hrs_highway,
       SUM(CASE WHEN chr.charge_name = 'KM CITY' THEN amount END) as km_city,
       SUM(CASE WHEN chr.charge_name = 'KM HIGHWAY' THEN amount END) as km_highway,
       SUM(CASE WHEN chr.charge_name = 'MISC' THEN amount END) as misc
FROM [tms].[orders] o JOIN
     [tms].[extra_charges_details] chr
     ON o.sourcetms = chr.sourcetms AND
        o.order_id = chr.[order] JOIN
     [tms].[probills] p
     ON o.sourcetms = p.sourcetms AND
        o.order_id = p.order_id JOIN
     [tms].[customers] c 
     ON o.UniqueCustomerID=c.UniqueCompanyID
WHERE o.[date] >= '2021-01-01'
GROUP BY o.order_id

Upvotes: 2

Related Questions