1

I am facing a problem in Unpivot the table.

My table structure is

Code     Level1Name Level1Email Level2Name Level2Email Level3Name Level3 Email
1         aa        aa@b.com     bb          bb@b.com     cc        cc@c.com

I need to convert this columns into Rows and need output like below

Code  Info    Level1       Level2        Level3 
1     Name     aa           bb            cc
1     Email    aa@b.com    bb@b.com       cc@c.com
jarlh
  • 42,561
  • 8
  • 45
  • 63
Jawahar
  • 13
  • 3
  • Does this answer your question? [Unpivot with column name](https://stackoverflow.com/q/19055902/2029983). What *have* you tried? Why didn't it work? If you don't understand the syntax, what about it don't you understand? – Thom A Feb 03 '21 at 11:02
  • I used this code for the above scenario . It works for one Level ,but when i tried this for another level it not work. And the name want to be same for the 2 levels select code,Info,Level1 from table Unpivot ( Level 1 for Info in (Level1Name,Level1Email) UNION ) as UnPvt select code,Info,Level2 from table Unpivot ( Level 2 for Info in (Level2Name,Level2Email) ) as UnPvt 1 – Jawahar Feb 03 '21 at 11:08
  • Put your attempts in the question, not the comments. Use the [edit] feature. – Thom A Feb 03 '21 at 11:12
  • Can u help me with this @Larnu – Jawahar Feb 03 '21 at 11:16
  • With using the [edit] feature? Click the hyperlink, and then put your attempt in there, using the copy and paste feature, inside a code fence (`\`\`\``). – Thom A Feb 03 '21 at 11:18
  • Perhaps `UNION ALL`? – jarlh Feb 03 '21 at 11:27

1 Answers1

1

I would use apply

select t.code, v.*
from t cross apply
     (values ('Name', t.Level1Name, t.Level2Name, t.Level1Name, Level3Name),
             ('Email', t.Level1Email, t.Level1Email, t.Level2Email, Level3Email)
     ) v(info, level1, level2, level3);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786