Dynamic Pivot Two Columns With Join


  

 CREATE TABLE #Table (Code varchar(50),Qty Float,SupName VARCHAR(10),SupQty float,SupPrice decimal(18,3))

insert into #Table VALUES ('Code1',10,'Supplier1',10,20)
insert into #Table VALUES ('Code1',10,'Supplier2',6,25)
insert into #Table VALUES ('Code2',10,'Supplier1',10,15)
insert into #Table VALUES ('Code3',10,'Supplier1',10,30)
insert into #Table VALUES ('Code3',10,'Supplier2',10,28)

DECLARE @cols1 AS NVARCHAR(2000), @cols2 AS NVARCHAR(2000), @colsResult AS NVARCHAR(2000), @cols4 AS NVARCHAR(2000)
,@sql AS NVARCHAR(4000)

SELECT @cols1 = STUFF((select DISTINCT ', ' + quotename(SupName ,']') FROM #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
SELECT @cols2 = STUFF((select DISTINCT ', ' + quotename(SupName +'2',']') FROM #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

SELECT @colsResult = STUFF((select DISTINCT ', '+ 'Max('+ quotename(SupName)+') as ' + quotename(SupName+'_Qty') +', '+ 'Max('+ quotename(SupName+'2')+') as ' + quotename(SupName+'_Price')
FROM  #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '') 

Set @sql=N';with mycte1 as (SELECT code, '+ @colsResult 
+  ' FROM ( Select Code,Qty,SupName,  SupName+''2'' as SupName2, SupQty,SupPrice FROM #Table) src  
PIVOT (Max(SupQty)   for SupName  IN ('+ @cols1 +')) pvt1
PIVOT (Max(SupPrice) for SupName2 IN ('+ @cols2 +')) pvt2
 Group by Code)
,mycte2 as (select Code,Qty,SupName, SupQty,SupPrice,row_number() Over(Partition by Code Order by SupPrice) rn
  FROM #Table)
,mycte3 as (Select Code,Qty,SupName as LowestSup, SupQty as LowestSupQty,SupPrice as LowestSupPrice from mycte2   Where rn=1)
Select m1.*, m3.Qty,m3.LowestSup,m3.LowestSupQty,m3.LowestSupPrice FROM mycte3 m3 JOIN mycte1 m1 ON m3.code=m1.code
'

 exec sp_executesql @sql;

CREATE TABLE #Result (Code varchar(50),Qty Float,Supplier1Qty float,Supplier1Price decimal(18,3),Supplier2Qty float,Supplier2Price decimal(18,3),LowestSup varchar(10),
LowestSupQty float,LowestSupPrice decimal(18,3))
insert into #Result VALUES ('Code1',10,10,20,6,25,'Supplier1',10,20)
insert into #Result VALUES ('Code2',10,10,15,NULL,NULL,'Supplier1',10,15)
insert into #Result VALUES ('Code3',10,10,30,10,28,'Supplier2',10,28)
select * from #Result

drop Table #Result
drop Table #Table

 

https://social.msdn.microsoft.com/Forums/en-US/4b99691b-e2a7-4cc0-a041-e4acb5d60c29/multiple-row-value-as-column-value?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