Unpivot Two Columns


  
create table salesorghierarchy(id int,sotl6Id varchar(20),sotl6desc  varchar(20)
, sotl5Id  varchar(20),sotl5desc  varchar(20), sotl4Id  varchar(20),sotl4desc  varchar(20))
insert into salesorghierarchy values (1,'a','a1','b','b1','c','c1'), (2,'a','a2','b','b2','c','c2') 

--option 1
select id, sotlIds,sotldescs 
from salesorghierarchy 
 cross apply(values ( sotl6Id,sotl6desc),(sotl5Id,sotl5desc),(sotl4Id,sotl4desc)) d(sotlIds,sotldescs)

 --option2
 Select  id, sotlId , sotldesc From (
Select   id, sotlId , sotldesc
  , idid = Left(Right(sotlIds,3),1)   
  , iddesc =  Left(Right(sotldescs,5),1)   	
	FROM ( select * from  salesorghierarchy ) src
  unpivot   (sotlId for sotlIds in  (sotl4Id, sotl5Id,sotl6Id ) ) unpvt1
  unpivot   (sotldesc for sotldescs in ([sotl4desc], [sotl5desc],[sotl6desc])) unpvt2 
 ) AS t
WHERE idid=iddesc;

--option 3
 select id, sotl4Id,sotl4desc 
from salesorghierarchy 
Union
 select id, sotl5Id,sotl5desc 
from salesorghierarchy 
Union
select id, sotl6Id,sotl6desc 
from salesorghierarchy 



drop table salesorghierarchy


 

https://social.msdn.microsoft.com/Forums/en-US/704c724f-7ad7-4e88-8532-43b5eb783a46/avoid-unions?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s