Dynamic Pivot With Parameter–(T-SQL)




If object_id('table1','U') is not null
drop table table1
 
create table table1 (PID int, Pname varchar(50), PartId int)
insert into table1 Values(0,'Length',1), (1,'Breadth',1),(2,'Height',1),(0,'Area',2),(1,'Volume',2)     

If object_id('table2','U') is not null
drop table table2

create table table2 (SampleID  int , PID int, Pvalue varchar(10), PartId int)
insert into table2  values (0,0,'10',1), (0,1,'10',1), (0,2,'fail',1), (1,0,'20',1),(1,1,'fail',1),(1,2,'fail',1), (0,0,'10',2), (0,1,'10',2)


--Assign your partid value here
 Declare @partId int=1

--SET @partId =2

 
DECLARE @col AS NVARCHAR(max)=''
DECLARE @sql AS NVARCHAR(max)
 
SELECT @col=stuff( (SELECT ',['+ PName  +']'
FROM (Select distinct top 100 Percent  PName, t2.Pid from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  Where t2.PartId=@PartId order by t2.Pid) p2
 
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
--print @col
 
SET @sql='SELECT [SampleID], ' + @col
+ ' FROM (
Select  SampleId, PName, PValue from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  
Where t2.PartId='+ Cast(@PartId as Nvarchar(50)) +' ) src
PIVOT (MAX(PValue) FOR [PName] IN ('+ @col + ')) AS pvt'

--print @sql
--EXEC(@sql)
EXEC sp_executesql @sql

 
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