Hi I need to display my data as per the following table:
Port_Description Vessel_Name LobstersKG Crabs (KG) Velvets(KG)
Aberdaron Mary Rose 3.5 0 0
Boy Paul 2.3 4.5 0
Aberdeen Lady Eleanor 4.5 2.3 0
Aberffraw Saorsa 4.6 2.5 0
I think I’m part of the way there but the following statement doesn’t give me the above table:
SELECT Port_Description, Vessel_Name, Common_Name,
case
when Common_Name = 'Lobsters' then ProductKiloQty
end
AS LobstersKG
FROM FormData
GROUP BY Port_Description, Vessel_Name, Common_Name, ProductKiloQty
It gives me this:
Port_Description Vessel_Name Common_Name LobstersKG
Aberdaron Mary Rose Lobsters 3.5
Boy Paul Crabs NULL
Boy Paul Lobsters 2.3
Aberdeen Lady Eleanor Crabs NULL
Lady Eleanor Lobsters 4.5
Aberffraw Saorsa Crabs NULL
Saorsa Lobsters 4.6
I tried adding to the case statement as follows:
SELECT Port_Description, Vessel_Name, Common_Name,
case
when Common_Name = 'Lobsters' then ProductKiloQty
end AS LobstersKG
case
when Common_Name = 'Crabs' then ProductKiloQty
end AS CrabsKG
FROM FormData
GROUP BY Port_Description, Vessel_Name, Common_Name, ProductKiloQty
But it didn’t work. Please can someone tell me how I can achieve this?