Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause):

  
  ;with u as (
      -- data setup
      select *
      from (
          values(1, 1, 1, 1),
              (2, 1, 1, 0),
              (3, 1, 0, 0),
              (4, 0, 0, 0)
      ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills)
  )
  select *
  from (
      select UserID, 0 as SkillType, 1 as DevelopmentSkill
      from u
      where HasDbSkills = 1
  union
      select UserID, 1 as SkillType, 1 as DevelopmentSkill
      from u
      where HasBackendSkills = 1
  union
      select UserID, 2 as SkillType, 1 as DevelopmentSkill
      from u
      where HasFrontEndSkills = 1
  ) t(UserID, SkillType, DevelopmentSkill)
  order by UserID, SkillType
  

Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:

  
;with userSkillMatrix as (
	-- data setup
	select *
	from (
		values(1, 1, 1, 1),
			(2, 1, 1, 0),
			(3, 1, 0, 0),
			(4, 0, 0, 0)
	) u(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills)
)
select UserID, SkillType, DevelopmentSkill
from (
	select UserID, HasDbSkills as [0], HasBackendSkills as [1], HasFrontEndSkills as [2]
	from userSkillMatrix
) skill_matrix
unpivot
(
	DevelopmentSkill for SkillType in ([0], [1], [2])
) AS skill_type_unpivot
where DevelopmentSkill <> 0
  

At the end of our session I challenged my mate to rewrite that piece, this time using CROSS APPLY. But that's a different story, which does not belong in this post.