Search code examples
sqlsql-server-2008-r2unpivot

perform unpivoting in a table by grouping 2 columns at once

I want to perform unpivoting in following table to obtain output as described below.

INPUT

====================================
| ID | Subj1 | Mark1 | Subj2 |Mark2|
====================================
|1   | Eng12 | 24    | Mth23 |NULL |
====================================
|2   | PSY42 | 54    |NULL   | NULL|
====================================

OUTPUT

====================
| ID | Subj | Mark |
====================
|1   | Eng12 | 24  |
====================
|1   | MTh23 | NULL|
====================
|2   | PSY42 | 54  |
====================
Solution

  • Based on your data and expected output

    SELECT
    id
    , subj1 subj
    , mark1 mark
FROM
    SomeTable
UNION ALL
SELECT
    id
    , subj2
    , mark2
FROM
    SomeTable
WHERE
    subj2 IS NOT NULL
;