SQLTeam.com | Weblogs | Forums

Dynamic pivoting for each column in master for all dates

tsql

#1

i have table tbl_product_details like :

create table tbl_product_details (productdate date, productname varchar(100), nav float , ncv float)

insert into tbl_product_details
values('2017-10-04 00:00:00', 'UU8899', 10.23, 13.89),
('2017-10-05 00:00:00', 'UU8899', 12.23, 14.89)

and one master table where name of required pivoting column stored like.

create table dynamiccols(id int , colname varchar(100))

insert into dynamiccols
values(1,'nav'),(1,'ncv')

i need to create dynamic pivoting to show pivoted data for all dates columns along with each column mentioned in dynamiccols
tables like :

productname nav04-10-2017 nav05-10-2017 ncv04-10-2017 ncv05-10-2017
UU8899 10.23 12.23 13.89 14.89

i have tried it but not able to make it complete dyanmic for each column in dynamiccols


DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName_header AS NVARCHAR(MAX)
DECLARE @ColumnName_pivot AS NVARCHAR(MAX)
declare @kpiname varchar(100)='ncv'

--Get distinct values of the PIVOT Column
SELECT @ColumnName_header= ISNULL(@ColumnName_header + ',','')
+ 'sum('+QUOTENAME(productdate) +') as ' ++QUOTENAME(@kpiname+'~~'+convert(varchar(11),productdate,105)),
@ColumnName_pivot= ISNULL(@ColumnName_pivot + ',','')
+ QUOTENAME(productdate)
FROM (SELECT DISTINCT productdate
FROM tbl_product_details
where productdate between '02-oct-2017' and '05-oct-2017') AS productdate

SET @DynamicPivotQuery =
N' select *
from ( SELECT productname ,' + @ColumnName_header + '
FROM tbl_product_details
PIVOT(sum('+@kpiname+')
FOR productdate IN (' + @ColumnName_pivot + ')) AS PVTTable
group by productname ) '+ char(97)

print @DynamicPivotQuery
exec(@DynamicPivotQuery)