Post reply

How to add column columnunit dynamically when FlagAllow is 1?

  • 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

  • June 19, 2020 at 4:10 am

    #3764304

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

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

  • June 19, 2020 at 12:31 pm

    #3764353

    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?

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

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