Search code examples
sqlsql-serverunpivot

SQL UnPivot Multiple Columns

Im Using SSMS and I have a table that looks like this;

ID   / Req1 / Req1Comment  / req2 / req2Commnet  /
1   /  yes /              / no   /  needs work  /
2  /  no  / not working  / yes  /              /

I would like that data to look like this for reporting purpose;

ID   / Requirement  / Requirement Status /  Comments
  1  /    Req 1    /   Yes              /
  1  /    Req 2   /    no               / Needs Work

I think I need to use unpivot but I cannot get the headers into the rows and the comments to line up total I have 25 Requirements and 25 Comment fields. on our paper form they have been static for years so I am not worried about future adding or removing new columns.

Solution

  • What stops you from using union of 25 selects?

    select ID, 'Req 1' as Requirement, Req1 as RequirementStatus, Req1Comment as Comments from t
union all
select ID, 'Req 2' as Requirement, req2 as RequirementStatus, req2Comment as Comments from t
union all
...
select ID, 'Req 25' as Requirement, req25 as RequirementStatus, req25Comment as Comments from t