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!

You don't want to UNPIVOT, you want to PIVOT. The question would be if you'll always have one or two rows? What happens if it has three or more? — Luis Cazares
– Luis Cazares, Commented Feb 28, 2019 at 22:07
Thanks, Luis. I will have anywhere from 1 to 2 for each entry, thus the NULL value I entered. The only way I can think of doing it is in VB, but I would prefer to do it in the SQL statement. — Douglas L Scheesley
– Douglas L Scheesley, Commented Feb 28, 2019 at 22:13
Do you actually need DISTINCT? What does the underlying data look like? — Dai
– Dai, Commented Mar 1, 2019 at 0:38

Dai · Accepted Answer · 2019-03-01 00:49:28Z

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

Wow, thank you so much, Dai! I really appreciate the improved syntax. One issue - I am getting a Syntax issue for the GROUP BY statement in the last query. Any ideas?
Hi @Dai, nevermind, I found the issue. I added an "AS" after the ")" of the nested function. Thank you so much, again! I learned so much from your explanation to up my SQL game.

Luis Cazares · Accepted Answer · 2019-03-01 14:48:41Z

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;

Collectives™ on Stack Overflow

How to pivot two rows into two columns

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related