How to add column columnunit dynamically when FlagAllow is 1?

Question

Post reply

How to add column columnunit dynamically when FlagAllow is 1?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

June 18, 2020 at 3:58 am

#3764086

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

as_1234 SSCrazy Points: 2842 More actions · Answer 2

I think this might be possible with a case statement. Can you explain how you split the two columns when flagallow=1? Is it always the right two characters from the tempvalue column?