How to add column columnunit dynamically when FlagAllow is 1?

  • ahmed_elbarbary.2010


    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

    create table #FinalTable
    DKFeatureName nvarchar(50),
    DisplayOrder int

    insert into #FinalTable (DKFeatureName,DisplayOrder)

    DECLARE @sh [dbo].[FeaturesbyPL];

    INSERT into @sh
    select Distinct DKFeatureName , DisplayOrder from #FinalTable
    declare @SQL NVARCHAR (MAX) = ''
    declare @Columns nvarchar(max)=( select
    Select ',['+ST1.DKFeatureName +']' AS [text()]
    From @sh ST1 order by DisplayOrder

    For XML PATH ('')
    ), 2, 10000) [Columns])
    select @Columns
    DECLARE @Header nvarchar(max)=( select
    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
    ''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


    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