How to add column columnunit dynamically when FlagAllow is 1?
How to add column columnunit dynamically when FlagAllow is 1?
(OP)
How to add column column unit dynamically when Flag Allow is 1 on table #nonparametric table ?
I work on SQL server 2012 I can't add column unit dynamically when Flag Allow=1
as example below I have two rows have Flag Allow=1
family 90AM will be family 90 and family unit AM on two column because it have Flag Allow=1
parametric 50.4kg will be parametric 50.4 and parametric unit kg on two column because it have Flag Allow =1
Expected Result for rows have Allow Flag=1 will be as below :

I work on SQL server 2012 I can't add column unit dynamically when Flag Allow=1
as example below I have two rows have Flag Allow=1
family 90AM will be family 90 and family unit AM on two column because it have Flag Allow=1
parametric 50.4kg will be parametric 50.4 and parametric unit kg on two column because it have Flag Allow =1
CODE --> sqlserver
create table #nonparametricdata ( PART_ID nvarchar(50) , CompanyName nvarchar(50), PartNumber nvarchar(50), DKFeatureName nvarchar(100), Tempvalue nvarchar(50), FlagAllow bit ) insert into #nonparametricdata values ('1222','Honda','silicon','package','15.50Am',0), ('1900','MERCEIS','GLASS','family','90.00Am',1), ('5000','TOYOTA','alominia','source','70.20kg',0), ('8000','MACDA','motor','parametric','50.40kg',1), ('8900','JEB','mirror','noparametric','75.35kg',0) create table #FinalTable ( DKFeatureName nvarchar(50), DisplayOrder int ) insert into #FinalTable (DKFeatureName,DisplayOrder) values ('package',3), ('family',4), ('source',5), ('parametric',2), ('noparametric',1) DECLARE @sh [dbo].[FeaturesbyPL]; INSERT into @sh select Distinct DKFeatureName , DisplayOrder from #FinalTable ------------------------------------------- declare @SQL NVARCHAR (MAX) = '' --------------------------------------- declare @Columns nvarchar(max)=( select substring( ( Select ',['+ST1.DKFeatureName +']' AS [text()] From @sh ST1 order by DisplayOrder For XML PATH ('') ), 2, 10000) [Columns]) select @Columns -------------------------------------------------- DECLARE @Header nvarchar(max)=( select substring( ( Select ', '''+ST1.DKFeatureName +''' as ['+ST1.DKFeatureName +']' AS [text()] From @sh ST1 order by DisplayOrder For XML PATH ('') ), 2, 10000) [Columns]) select @Header select @SQL =CONCAT(' SELECT * Into #NewTable2 FROM #nonparametricdata PIVOT(max(Tempvalue) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable ', N' select ''PART_ID'' as ''PART_ID'' ,''PartNumber'' as ''Z2 Part number'' , ''CompanyName'' as ''Z2 Manufacturer'' , ' +@Header + ' union all select PART_ID , PartNumber , CompanyName , ' +@Columns + ' from #NewTable2 ') EXEC (@SQL)
