Convert Rows using PIVOT in SQL-CodePudding

I'm trying to convert rows to columns (Based on different Years) in SQL with pivot function but cannot make it happened. Any help will be greatly appreciated.

Here is the original data and the expected outcome:

enter image description here

the original data:

Type |  Name |  Cost |  Year

A   NDA $385.00 FY22

A   ING $342.00 FY21

A   IEG $360.00 FY21

A   ERT $346.00 FY21

A   OVA $176.00 FY20

A   ZIO $310.00 FY20

A   ZIO $301.00 FY22

A   ZIO $237.00 FY21

A   ZIU $398.00 FY21

A   ZIU $338.00 FY20

A   ZIU $101.00 FY22

B   RLA $319.00 FY22

B   RLA $148.00 FY20

B   RLA $206.00 FY21

B   ACK $224.00 FY22

B   EZA $282.00 FY21

B   EON $187.00 FY21

B   EON $250.00 FY20

B   EGG $297.00 FY20

the expected outcome: enter image description here

Below is the code I'm using:

select Type, Name, sum(price) as Cost, Year

from
(
  select Type, Name, price, Year
  from ProductDB
) a

pivot
(
 ????
) as PivotTable;

CodePudding user response：

Conditional Aggregations give you a bit more flexibility, but if you are looking for the PIVOT

Select *
 From (
       select Type, Name, Cost, Year
        from ProductDB
       ) src
 Pivot ( sum(price) for Year in ( [FY20],[FY21],[FY22] ) ) pvt ;

Note: Assuming SUM(Cost) but you could use MAX(),AVG(), etc.

Conditional Aggregations

Select Type
      ,Name
      ,FY20  = sum( case when Year = 'FY20' then Cost end )         -- missing will show NULL
      ,FY21  = sum( case when Year = 'FY21' then Cost else 0 end )  -- missing will show 0
      ,FY22  = sum( case when Year = 'FY22' then Cost else 0 end )
From  ProductDB
Group By Type,Name

CodePudding user response：

SQLite is what I use and there's actually no PIVOT clause.

So I can solve this problem using CASE statements like this:

SELECT Type, Name, 
SUM(CASE WHEN Year is 'FY20' THEN Cost ELSE 0 END) as FY20,
SUM(CASE WHEN Year IS 'FY21' THEN Cost ELSE 0 END) as FY21,
SUM(CASE WHEN Year is 'FY22' THEN Cost ELSE 0 END) as FY22
FROM original_data
GROUP BY Name;

Hope that Helps!