Combine results of SQL Query

Question

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!

Cant you just group by Header.Customer then MAX() the other columns? — Brad C, Jul 09 '15 at 18:39
looks like a pivot question: https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — wero, Jul 09 '15 at 18:42

score 2 · Accepted Answer · answered Jul 09 '15 at 18:42

2

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

answered Jul 09 '15 at 18:42

Gordon Linoff

1,242,037
58
646
786

+1 for the example. This solution works very well however, I haven't taken a look at the execution plans yet to see how much of a performance impact the aggregates caused. – Tama198 Jul 09 '15 at 19:06

Combine results of SQL Query

1 Answers1