Qfyilyi

So I am trying to pull the data from the table to give me each score the person has and if they have two scores I would like it to be on a new line with the second score. If the user has no scores I don't want anything returned. My query returns the first score if the user has one and if they don't it returns the second one. But if the user has two scores is where i'm not sure how to return that one on a new line.

table 1

+---------+--------+--------+

|  name   | score1 | score2 |

+---------+--------+--------+

| jim     | null   | 87     |

| doug    | 21     | 45     |

| brandon | null   | null   |

| susy    | 11     | null   |

+---------+--------+--------+

The result my query gives is

+------+----+

| jim  | 87 |

| doug | 21 |

| susy | 11 |

+------+----+

Wanted output

+------+----+

| jim  | 87 |

| doug | 21 |

| doug | 45 |

| susy | 11 |

+------+----+

The query I wrote is

SELECT

  name

 ,COALESCE(score1, score2)

FROM

  table

WHERE

  score1 IS NOT NULL

  OR score2 IS NOT NULL

ORDER BY

  name;

Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.

SELECT name, Score1 as score

    FROM table1

    WHERE Score1 IS NOT NULL

UNION ALL

SELECT name, Score2 as score

    FROM table1

    WHERE Score2 IS NOT NULL 

ORDER BY name, score;  

I would recommend cross apply:

SELECT t.name, v.score

FROM table t CROSS APPLY

     (VALUES (score1), (score2)) v(score)

WHERE v.score IS NOT NULL

ORDER BY name;

This is usually the most efficient way to unpivot data in SQL Server.