Search code examples
sqloracle-databaseunpivot

Toad for Oracle- Reshape Wide to Long

I am trying to transpose a data set from wide to long. Ive done this is SAS but am new to this. Ideally I want one unique row per ID and code. Not sure if it can be done in one step or multiple, either way would be fine with me. My data looks something like this.

ID   code1   code2   code3
1     abc     def     ghi
1     abc     xyz     def
2     zyx     abc     mno

AND I want it to look like

ID  Code_concat
1   abc
1   def
1   ghi
1   xyz
2   zyx
2   abc
2   mno

Any advice would be greatly appreciated, thanks!

Solution

  • The simplest method given that you want to remove duplicates is union:

    select id, code1 as code_concat from t union
select id, code2 as code_concat from t union
select id, code3 as code_concat from t;