SQL UnPivot Multiple Columns

Question

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.

Could be done using UNION ALL.

jarlh
– jarlh

2020-03-23 09:47:02 +00:00
Commented Mar 23, 2020 at 9:47 — jarlh
– jarlh, Commented Mar 23, 2020 at 9:47

Tomáš Záluský · Accepted Answer · 2020-03-23 09:51:43Z

2

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

answered Mar 23, 2020 at 9:51

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Greg Schalk Over a year ago

Thank you! I applied this and a few mins of work the view was created and the report is ready to build!

Gordon Linoff · Accepted Answer · 2020-03-23 11:26:34Z

2

I would suggest just using cross apply:

select t.id, v.*
from t cross apply
     (values ('Req 1', Req1, Req1Comment),
             ('Req 2', Req2, Req2Comment),
             . . .
     ) v(requirement, status, comments);

If you don't want to type 25 lines for values, you can use a spreadsheet to generate the code.

answered Mar 23, 2020 at 11:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

lptr · Accepted Answer · 2020-03-23 13:17:12Z

declare @t table
(
id int identity,
Req1 varchar(10),
Req1Comment varchar(20),
Req2 varchar(10),
Req2Comment varchar(20)
)

insert into @t (Req1, Req1Comment, Req2, req2Comment)
values
('yes', null, 'no', 'needs work'),
('no', 'not working', 'yes', '');


select id, 'Req 1' as Requirement, Req1 as [Requirement Status], Req1Comment as Comments
from @t
union all
select id, 'Req 2', Req2, Req2Comment
from @t;

select id, concat('Req ', v.val) as Requirement, case v.val when 1 then Req1 else Req2 end as [Requirement Status], case v.val when 1 then Req1Comment else Req2Comment end as Comments
from @t as t
cross apply (values(1), (2)) as v(val);

select id, concat('Req ', v.val) as Requirement, 
    choose(v.val, Req1, Req2) as [Requirement Status], 
    choose(v.val, Req1Comment, Req2Comment) as Comments
from @t as t
cross apply (values(1), (2)) as v(val);

Collectives™ on Stack Overflow

SQL UnPivot Multiple Columns

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related