How to add column columnunit dynamically when FlagAllow is 1?

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2698

    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

  • Site Owners

    SSC Guru

    Points: 80375

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

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

  • as1981

    SSCrazy

    Points: 2744

    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