troubleshooting Question

Problems with Case statement and grouping.

Avatar of Lorna70
Lorna70 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
4 Comments2 Solutions268 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros