CODEWITHSUNDEEP
sqlsql-serverstored-proceduresunpivotlateral-join
Stormfrazier
Stormfrazier

Reputation: 1

Unpivoting two separate columns

I am trying to Unpivot multiple columns from a table. So far I have tried to just use a standard Unpivot and it is successful for the first part, but not the second. I would like to have both columns Unpivote. I have attached a picture of what I am trying to do. The table has to do with errors. So E1 is short for error1, E2 short for error2 etc...

INSERT INTO #tempWorkflowItem
SELECT AssignmentId, Code, Response FROM #temp2
UNPIVOT(Response FOR Code in (E1, E2, E3 ))AS WorkflowItemsUnpivot
UNPIVOT(Reason FOR Code in (E1Reason, E2Reason, E3Reason )) AS WorkflowItemsUnpivot2

Table Conversion

Upvotes: 0

Views: 39

Answers (2)

GMB
GMB

Reputation: 222582

I would recommend cross apply:

select x.*
from mytable t
cross appy (values 
    ('e1', e1, e1reason),
    ('e2', e2, e2reason),
    ('e3', e3, e3reason)
) x(code, response, reason)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use apply:

select v.code, v.reason
from #temp2 t cross apply
     (values ('E1', E1Reason), 
             ('E2', E2Reason), 
             ('E3', E3Reason)
     ) v(code, reason, e1, e2, de3);

I suspect you also want a where clause:

where v.code = 'E1' and t.e1 > 0 and
      v.code = 'E2' and t.e2 > 0 and
      v.code = 'E3' and t.e3 > 0 ;

or:

where v.reason is not null

It seems odd to be pulling the NULL values back as well.

Upvotes: 1

Related Questions