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)