CODEWITHSUNDEEP
sqlsql-server-2008-r2
Tama198
Tama198

Reputation: 29

Combine results of SQL Query

I have done some looking into a seemingly simple task but haven't been able to find a simple solution. I wanted to ask this question in the most simplest terms so that others, and myself, may benefit from similar situations. I came across a query that needed to be refactored a little that produced some strange results.

What would be the best way to turn these results:

ID    Customer    CustomerID (US)    CustomerID (CA)   CustomerID (EU)
-----------------------------------------------------------------------
1     XYZ         XYZ - US           NULL              NULL
2     XYZ         NULL               XYZ - CA          NULL
3     XYZ         NULL               NULL              XYZ - EU

Into this:

ID    Customer    CustomerID (US)    CustomerID (CA)   CustomerID (EU)
-----------------------------------------------------------------------
1     XYZ         XYZ - US           XYZ - CA          XYZ - EU

If there is more than one approach, what would be the best way following the most current best practices?

Here is some sample code that currently produces the first results:

    SELECT Header.ID,
        Header.Customer,
        CASE Detail.Location
            WHEN 'US' THEN Detail.CustomerID
            ELSE NULL
        END AS [CustomerID (US)],
        CASE Detail.Location
            WHEN 'CA' THEN Detail.CustomerID
            ELSE NULL
        END AS [CustomerID (CA)],
        CASE Detail.Location
            WHEN 'EU' THEN Detail.CustomerID
            ELSE NULL
        END AS [CustomerID (EU)]
    FROM dbo.Header AS Header 
        LEFT OUTER JOIN dbo.Detail AS Detail
            ON Header.ID = Detail.HeaderID

Many thanks for your help!

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You just want aggregation:

SELECT MIN(Header.ID) as ID, Header.Customer,
       MAX(CASE Detail.Location WHEN 'US' THEN Detail.CustomerID
           END) AS [CustomerID (US)],
       MAX(CASE Detail.Location WHEN 'CA' THEN Detail.CustomerID
           END) AS [CustomerID (CA)],
       MAX(CASE Detail.Location WHEN 'EU' THEN Detail.CustomerID
           END) AS [CustomerID (EU)]
FROM dbo.Header LEFT OUTER JOIN
     dbo.Detail Detail
     ON Header.ID = Detail.HeaderID
GROUP BYHeader.Customer

Upvotes: 2

Related Questions