How to pivot two rows into two columns

Question

I have the following SQL Query:

select
    distinct

    Equipment_Reserved.Equipment_Attached_To,
    Equipment.Name
from
    Equipment,
    Studies,
    Equipment_Reserved
where
    Studies.Study = 'MAINT19-01'
    and
    Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment
    and
    Studies.idStudies = Equipment_Reserved.Studies_idStudies
    and
    Equipment.Type = 'Probe'

This query produces the following results:

Equipment_Attached_To   Name
2297                    R1-P1
2297                    R1-P2
2299                    R1-P3

I would like to change it to the following:

Equipment_Attached_To   Name1     Name2
2297                    R1-P1     R1-P2
2299                    R1-P3     NULL

Thanks for your help!

Answer 1

Let's start with some basics.

• To facilitate reading the code, I added alias to the tables using their initials.
• Then, I converted the old join syntax which is partly deprecated to use the standard syntax since 1992 (27 years and people still use the old syntax).
• Finally, since there are only 2 possible values, we can use MIN and MAX to separate them in 2 columns.
• And because we're using aggregate functions, we remove the DISTINCT and use GROUP BY The code now looks like this:

    SELECT er.Equipment_Attached_To, 
        --Gets the first row for the id
        MIN( e.Name) AS Name1, 
        --If the MAX is equal to the MIN, returns a NULL. If not, it returns the second value.
        NULLIF( MAX(e.Name), MIN( e.Name)) AS Name2 
    FROM Equipment e
    JOIN Studies s ON s.idStudies = er.Studies_idStudies
    JOIN Equipment_Reserved er ON e.idEquipment = er.Equipment_idEquipment
    WHERE s.Study = 'MAINT19-01'
    AND e.Type = 'Probe'
    GROUP BY er.Equipment_Attached_To;

Answer 2

I'd first change your query from the old, legacy JOIN syntax to an explicit join as it makes the query easier to understand:

SELECT
    DISTINCT

    Equipment_Reserved.Equipment_Attached_To,
    Equipment.Name
FROM
    Equipment
    INNER JOIN Equipment_Reserved ON Equipment_Reserved.Equipment_idEquipment = Equipment.idEquipment
    INNER JOIN Studies            ON Studies.idStudies = Equipment_Reserved.Studies_idStudies

WHERE
    Studies.Study = 'MAINT19-01'
    AND
    Equipment.Type = 'Probe'

I don't think you actually need a PIVOT - I think you can do this with a nested query with the ROW_NUMBER function. I've seen that PIVOT queries often have worse query execution plans than nested-queries.

Let's add ROW_NUMBER (which require an ORDER BY as it's a windowing-function) and a matching ORDER BY in the whole query to make it consistent). Let's also use PARTITION BY so it resets the row-number for each Equipment_Attached_To value:

SELECT
    DISTINCT
    Equipment_Reserved.Equipment_Attached_To,
    Equipment.Name,

    ROW_NUMBER() OVER (PARTITION BY Equipment_Attached_To ORDER BY [Name]) AS RowNumber

FROM
    Equipment
    INNER JOIN Equipment_Reserved ON Equipment_Reserved.Equipment_idEquipment = Equipment.idEquipment
    INNER JOIN Studies            ON Studies.idStudies = Equipment_Reserved.Studies_idStudies

WHERE
    Studies.Study = 'MAINT19-01'
    AND
    Equipment.Type = 'Probe'

ORDER BY
    Equipment_Attached_To,
    [Name]

This will give output like this:

Equipment_Attached_To   Name    RowNumber
2297                    R1-P1    1
2297                    R1-P2    2
2299                    R1-P3    1

This can then be split out into explicit columns like so below. The use of MAX() is arbitrary (we could use MIN() instead) and only because we're dealing with a GROUP BY and because the CASE WHEN... restricts the input set to just 1 row anyway.

SELECT
    Equipment_Attached_To,
    MAX( CASE WHEN RowNumber = 1 THEN [Name] END ) AS Name1,
    MAX( CASE WHEN RowNumber = 2 THEN [Name] END ) AS Name2
FROM
    (
        -- the query from above
    )
GROUP BY
    Equipment_Attached_To
ORDER BY
    Equipment_Attached_To,
    Name1,
    Name2

So the final query is:

SELECT
    Equipment_Attached_To,
    MAX( CASE WHEN RowNumber = 1 THEN [Name] END ) AS Name1,
    MAX( CASE WHEN RowNumber = 2 THEN [Name] END ) AS Name2
FROM
    (
        SELECT
            DISTINCT
            Equipment_Reserved.Equipment_Attached_To,
            Equipment.Name,

            ROW_NUMBER() OVER (PARTITION BY Equipment_Attached_To ORDER BY [Name]) AS RowNumber

        FROM
            Equipment
            INNER JOIN Equipment_Reserved ON Equipment_Reserved.Equipment_idEquipment = Equipment.idEquipment
            INNER JOIN Studies            ON Studies.idStudies = Equipment_Reserved.Studies_idStudies

        WHERE
            Studies.Study = 'MAINT19-01'
            AND
            Equipment.Type = 'Probe'
    )
GROUP BY
    Equipment_Attached_To
ORDER BY
    Equipment_Attached_To,
    Name1,
    Name2