Dynamic Pivot Sample with Two Column Lists

  
 
Create table #MAIN (SAPID char(10), Col1 varchar(20), Col2 varchar(20), Col3 varchar(20), SAP1 varchar(10), City varchar(20))
Insert into #MAIN values ('SADRT','4000','8000','120000','2892999','IL')
Insert into #MAIN values ('HSGYD','4000','8000','100000','2892999','PL')
Insert into #MAIN values ('JUHYD','6000','8000','120000','3837737','SL')

Create table #Meaning (SID int, Col_Name varchar(20), Col_Desc varchar(40))
Insert into #Meaning values (1,'Col1','Toolprice')
Insert into #Meaning values (2,'Col2','Machineprice')
Insert into #Meaning values (3,'Col3','Microscopeprice')
Insert into #Meaning values (4,'Col4','Furnaceprice')
Insert into #Meaning values (5,'Col5','Velcrotapeprice')
Insert into #Meaning values (6,'Col6','Packingprice')
Insert into #Meaning values (7,'SAP1','Wrappingprice')

DECLARE @cols1 AS NVARCHAR(2000),  @cols2 AS NVARCHAR(2000), @sql AS NVARCHAR(4000)
 
select @cols1 = STUFF((select DISTINCT ', '
+ quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))
FROM  #Meaning 
where Col_name in ('Col1','Col2','Col3','SAP1')
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

--Print @cols1

select @cols2 = STUFF((select DISTINCT ', '
+ 'Max('+quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))+') as ' +quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))
FROM  #Meaning 
where Col_name in ('Col1','Col2','Col3','SAP1')
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @cols2
 
Set @sql=N';with mycte as (
select SAPID,Col,Val,City from #MAIN
cross apply (values(''Col1'',Col1),(''Col2'',Col2),(''Col3'',Col3),(''SAP1'',SAP1) ) d(col,val)
)

SELECT SAPID, '+ @cols2 + ' ,City FROM (
Select (rtrim(col_name)+''_''+rtrim(COl_Desc)) as cols,SAPID,Col,Val,City
FROM mycte m join #Meaning me on m.Col=me.Col_Name
) src
PIVOT (Max(val) for Cols IN ('+ @cols1 +')) pvt
Group by SAPID,City'
 --print @sql
exec sp_executesql @sql;

drop table #Meaning,#MAIN
 

 

https://social.msdn.microsoft.com/Forums/en-US/3914a400-417d-43d2-b19c-2da598823dd6/help-in-query-please?forum=transactsql

---