CODEWITHSUNDEEP
sqlunionunpivot
user1180202
user1180202

Reputation: 25

SQL UNPIVOT? is this what i should be using?

So I have this data structure 

ID  DATE1     STATUS1 DATE2    STATUS2
1   1/1/2012  1       1/1/2012 3
1   3/1/2012  1       1/2/2012 3 
2   2/1/2012  1       3/4/2012 3
3   4/1/2012  1       NULL     3
4   2/1/2012  1       6/2/2012 3

How would I get to...

ID  DATE      STATUS
1   1/1/2012  1
1   1/1/2012  3
1   3/1/2012  1       
1   1/2/2012  3
2   2/1/2012  1       
2   3/4/2012  3
3   4/1/2012  1       
3   NULL      3
4   2/1/2012  1       
4   6/2/2012  3

My initial guess might be to use lots of unioned unpivots?

Upvotes: 1

Views: 388

Answers (3)

StevieG
StevieG

Reputation: 8709

You can use union for this:

select id, date1 mydate, status1 status
from mytable
union all
select id, date2 mydate, status2 status
from mytable

if you want to remove dupes, just replace union all with union

Note: I've named the resulting date column mydate because date is probably a reserved keyword in whichever dbms you're using..

Upvotes: 1

Diego
Diego

Reputation: 36136

I think in your case a simple UNION would help:

select ID,  DATE1, STATUS1 FROM your_table
union 
select ID, DATE2, STATUS2 FROM your_table

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

UNPIVOT is not required here. Plain old UNION ALL will suffice.

   SELECT ID, DATE1 DATE, STATUS1 STATUS
     FROM TBL
UNION ALL
   SELECT ID, DATE2 DATE, STATUS2 STATUS
     FROM TBL
 ORDER BY ID, DATE, STATUS

Upvotes: 1

Related Questions