Search code examples
sqlsql-servert-sqlunpivot

Can I avoid using union all statements?

I've got an extract out of a system that shows the data in excel in the following format:

Like this

I've used lengthy union statement to get to the following outcome:

Like this

I'm wondering is there a better way that's less messy and easier to read over than a whole bunch of union all statements?

Solution

  • If you are working with SQL Server then cross apply would be better approach then UNION ALL statement 

    select a.* from table t
cross apply (
       values (PersonId, 'Q1', Q1)
             ,(PersonId, 'Q2', Q2)
             ,(PersonId, 'Q3', Q3)
             ,(PersonId, 'Q4', Q4)
               ...
               ...
             ,(PersonId, 'Q20', Q20)
)a(PersonId, Q, Outcome)
order by a.Q, a.PersonId