CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
NS01
NS01

Reputation: 37

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?

Upvotes: 1

Views: 110

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 1

Related Questions