sivaj2k , Mitesh Oswal , David Burrows - thank you for your replies, will test them later today and reply to this post with my findings.

-----------------------------------------

Below is a much simpler example, which illustrates what I require and also illustrates how to achieve the result.

Sample data:

OrderID = 1,2,3

Manager = 'Rafael','Vladimir','Ulri'

Worker = 'Elena','Julia','Jenny'

SELECT 1, 'Rafael', 'Elena' UNION ALL

SELECT 2, 'Vladimir', 'Julia' UNION ALL

SELECT 3, 'Ulri', 'Jenny'

-----------------------------------------

Basic select statement to list data:

SELECT

[OrderID]

,[Manager]

,[Worker]

FROM [TEST].[dbo].[Unpivoting]

-----------------------------------------

How to unpivot the data:

SELECT OrderID, Employee = Manager,

CASE WHEN [Manager] IS NOT NULL THEN 'Manager' END AS Statuses

FROM [dbo].[Unpivoting]

UNION ALL

SELECT OrderID, Employee = Worker,

CASE WHEN [Worker] IS NOT NULL THEN 'Worker' END AS Statuses

FROM [dbo].[Unpivoting]

ORDER BY OrderID;