Help Restructuring a Table

Other SQL Server Topics
1

Hello!
I am new here.
I need some help restructuring a table...but I don't know which SQL logic to use.
Here is a snapshot of what I am trying to do.

image
image831×460 11 KB

Any help would be greatly appreciated!
Thank you!
Have a great day!

2

Welcome

Please provide your data not as an image but data we can use without having to retype it all

declare @sample table(Class varchar(50), 
Animal1 varchar(50),
Animal2  varchar(50),
Animal3  varchar(50),
Animal4  varchar(50))

insert into @sample
values('reptile','crocodile','lizard','snake','aaa')
3

Here you go!

class animal1 animal2 animal3 animal4
reptile crocodile lizard snake alligator
bird penguin ostrich emu owl
mammal lion tiger bear

Let's suppose the table name is: animal

4

Not gonna retype all of that for you, please follow the below approach
exactly

declare @sample table(Class varchar(50), 
Animal1 varchar(50),
Animal2  varchar(50),
Animal3  varchar(50),
Animal4  varchar(50))

insert into @sample
values('reptile','crocodile','lizard','snake','aaa')
5 
declare @sample table(Class varchar(50), 
Animal1 varchar(50),
Animal2  varchar(50),
Animal3  varchar(50),
Animal4  varchar(50))

insert into @sample
values('reptile','crocodile','lizard','snake','alligator')

insert into @sample
values('bird','penguin','ostrich','emu','owl')

insert into @sample
values('mammal','lion','tiger'','bear','')
1 Like
6

one way


SELECT [Animal],
Class
FROM @sample
UNPIVOT (
          [Animal] FOR [Animals] 
          IN ([Animal1],[Animal2],[Animal3],[Animal4]) 
	) AS [UNPIVOT TABLE]

2nd way


SELECT src.Class 
      ,u.[Animal]  
FROM @sample AS src
CROSS APPLY 
 (
   VALUES 
         ([Animal1], 'Animal1')
        ,([Animal2], 'Animal2')
        ,([Animal3], 'Animal3')
        ,([Animal4], 'Animal4')
 ) u ([Animal], [Animals] )