How to add column columnunit dynamically when FlagAllow is 1?

Transact-SQL
,
1

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

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)

Expected Result for rows have Allow Flag=1 will be as below :slight_smile:

image
image1360×768 9.45 KB

2

Hi

not sure if i understand your requirement !!!

why dynamic SQL making it complicated ..

simple Create New Table .. with new columns !!
:slight_smile:

3

Here's a dynamic version assuming that if FlagAllow = 1 then it will split the Temp value into 2 fields

DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT DKFeatureName  
FROM #nonparametricdata 
  
SELECT @ColsList = COALESCE(@ColsList + ',Case when DKFeatureName = ''', 'Case when DKFeatureName = ''') + Head + ''' then Case when FlagAllow = 1 then	left(TempValue, Len(TempValue) - 2) else TempValue end else Null end as ' + Head + 
			', Case when DKFeatureName = ''' + Head + ''' then Case when FlagAllow = 1 then	Right(TempValue, 2)	else Null end else Null end as ' + Head + 'Unit'
FROM @Cols t  


exec('Select Part_ID, PartNumber, CompanyName,' + @ColsList + ' from #nonparametricdata')
4

thank you for reply
not exactly what I need
it is wrong because it add column units for rows have flag=1 or flag=0
but i need to add column units for rows have flag=1
and rows have flag=0 no need see image below it show what i need

image
image1360×768 29.6 KB